-1

I was hoping someone could explain this a bit better for me. I have a visual studio project and created the database in the project: Project >> Add Item >> Service Database. I have a form with a textbox that I am trying to insert data into and I have looked up how to do this and there are things like SQLCommand or ExecuteNonQuery are not an option I have.

Since the database is associated with the project I don't know if I even need to do that part but I haven't seen anything to the contrary. I don't want to hard code in a server connection if I can avoid it because I am hoping this will become an application.

This is my code so far

Private Sub btnAddNewSpellSchool_Click(sender As Object, e As EventArgs) Handles btnAddNewSpellSchool.Click
    Dim sqlCMD As String
    Dim text As String

    text = Me.txtAddSpellSchool.Text

    sqlCMD = "INSERT INTO tblList_Spell_Config_SpellSchool (spellSchool) VALUES('" & text & "')"

End Sub

This is what I have been seeing

Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter

DA.InsertCommand = New SqlCommand("Insert Into tbl1(fld0, fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter ("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld2", SqlDbType.NVarChar, 50, "fld2"))
DA.Update(dataset1, "tbl1")
Jake
  • 303
  • 5
  • 19
  • What do you mean with _things like SQLCommand or ExecuteNonQuery are not an option I have._ Do you have added the reference to the assembly Microsoft.Data.SqlClient (or System.Data.SqlClient)? – Steve Jul 03 '22 at 16:49
  • So I have a data source connection to the database. I created it though Project >> Add Data Source. I was able to get the table grid in with nothing populated. – Jake Jul 03 '22 at 17:16
  • The following may be helpful [SQL Server Express LocalDB](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver16) and [Create a database and add tables in Visual Studio](https://docs.microsoft.com/en-us/visualstudio/data-tools/create-a-sql-database-by-using-a-designer?view=vs-2022). Also [SQL Server Downloads](https://www.microsoft.com/en-us/sql-server/sql-server-downloads) and [Download SQL Server Management Studio (SSMS)](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16) – Tu deschizi eu inchid Jul 03 '22 at 17:32
  • Here's another one: [SQL Server Express vs express localdb](https://stackoverflow.com/questions/42028186/sql-server-express-vs-express-localdb) – Tu deschizi eu inchid Jul 03 '22 at 17:37
  • @user9938 I have read all of those and I do create the tables in Visual Studio. I have SQL Server 2019 running currently. The issue was that I could not get the LocalDB to attach to the project and from what I could tell from the research the SQL Service would be better for an application as well as building the database inside the project. – Jake Jul 03 '22 at 17:51

1 Answers1

0
Imports System.Data
Imports System.Data.SqlClient
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim myconn As SqlConnection
    Dim mycmd As SqlCommand
    Dim qry As String

    qry = "Insert Into tblList_Spell_Config_SpellSchool (spellSchool) Values('Air')"

    myconn = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\jpac0\Dropbox\Dungeon and Dragons\DND_Application\DND_ExperienceBuilder\DND_ExperienceBuilder\DND_ExperienceBuilderDB.mdf;Integrated Security=True;Connect Timeout=30")
    myconn.Open()
    mycmd = New SqlCommand(qry, myconn)
    mycmd.ExecuteNonQuery()
    myconn.Close()
End Sub
Jake
  • 303
  • 5
  • 19