Resolved ...Insert Data in an SQL database

[Tomson]

Can anyone tell me what is wrong with the code below. I have a database, with a table called LineInfo. It has 2 fields named Jobnum and LineNum. I am trying to insert a record but it will not do it. I must be missing something. The GetConnection works fine.

As always any help is greatly appreciated.

Thanks,
Tomson

Public Sub GetConnection()
Dim connectionString As String = "Integrated Security= SSPI;server=Jack;database=MyDataBase"
myConnection = New System.Data.SqlClient.SqlConnection(connectionString)
myConnection.Open()
End Sub


Public Sub InsertData()
Dim myDataSet1 As System.Data.DataSet
Dim myDataTable1 As DataTable
Dim dtv As DataRow
Dim myTable as string
Dim commandString as string

GetConnection()
commandString = "SELECT LineInfo.jobNum,LineInfo.lineNum FROM LineInfo WHERE JobNum = '" & jobNum & "'"

myTable = "LineInfo"
myDataSet1 = New System.Data.DataSet
myDataSet1.CaseSensitive = True

myCommand = New System.Data.SqlClient.SqlCommand
myCommand.Connection = myConnection
myCommand.CommandText = commandString

myDataAdapter = New System.Data.SqlClient.SqlDataAdapter
myDataAdapter.SelectCommand = myCommand
myDataAdapter.TableMappings.Add("Table", myTable)

myDataAdapter.Fill(myDataSet1)
myDataTable1 = myDataSet1.Tables(0)
myCommand.Dispose()

myCommand = New System.Data.SqlClient.SqlCommand
myCommand.Connection = myConnection

commandString1 = "insert into Lineinfo(jobnum) values (123456)"

myCommand.CommandText = commandString1
dtv = myDataTable1.NewRow

myDataAdapter.InsertCommand = myCommand
myDataTable1.Rows.Add(dtv)
myDataAdapter.Update(myDataSet1, myTable)

myDataAdapter.Dispose()

End Sub