Resolved ...Insert Data in an SQL database

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

I'm not an expert on that method, and haven't used SQL server, just Access, but one thing to always check is that the datatype of the field in the table matches the datatype of the value to insert. Since you have parantheses around the numbers, I'd guess you're inserting text. Is it supposed to be a numeric field? If 123456 is a number, get rid of the parantheses. If text, it may have to be surrounded by single quotes.
Also, don't know if you're required to supply a value, even if only null or empty, for all fields when adding a new row.
I could be way off base here, but that's what I'd try first. Maybe post what the error message says.

I have done your suggestions, and I thank you for them. But the data types match the database data types. I also tried puting the single quotes around the value without the double quotes around the value. Didn't work either. Here is the error message (for what its worth) that I get when it tries to execute

myDataAdapter.Update(myDataSet1, myTable)

Error message:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Do you have an easier way of doing this via a dataadapter maybe?

Thanks again for your help in this matter.
Tomson

Well, so far I've just used SQL with ExecuteNonQuery to do an INSERT. I'd post an example but I'm not at work.
Put your code in a Try...Catch block, and when you catch the exception, messagebox ex.tostring to get a complete error description, or maybe just ex.message for a shorter version (assuming you use ex as your exception variable).

i think you are getting datasets and commands mixed up.

If you want to run your insert sql string you might need to set the commandType
myCommand.commandType = commandType.text

and then call
myCommand.executeNonQuery

this bit in your code
dtv = myDataTable1.NewRow

creates a new row the same structure as those already in your dataset. You then need to fill this row with values, and then add it to your dataset. You can then call the update method of the dataAdapter. Also, your table should have a primary key, this will means the dataAdapter automatically generates insert and update commands for you.

You're probably better off using nswan's method, since I'm still learning vb.net, but here's how I did an INSERT. mstrCn is the global conection string. I didn't use bound controls.

VB Code:

Private Function SaveData(ByVal blnIsCAS As Boolean, ByVal blnIsSent As Boolean, ByVal blnIsNotSent As Boolean) As Boolean
        Dim strSQL, strErrMsg As String
        Dim cmd As OleDbCommand
        Dim cn As OleDbConnection
 
        SaveData = True 'initialize 
 
        'Build SQL string based on whether project is CAS.
        'Including service date on CAS raises cast error when no date present.
        strSQL = "INSERT INTO WhiteSlips "
        If blnIsCAS Then
            strSQL += "([Database], [ChooseProject], [From], [Subject], [SurveyNumber], [Month], [PatientNumber], [Area/Page], [PatientName], [ComputerNumber], [PatientPhone], [InterviewerNumber], [Message], [Sent], [NotReallySent]) "
        Else
            strSQL += "([Database], [ChooseProject], [From], [Subject], [SurveyNumber], [Month], [PatientNumber], [Area/Page], [PatientName], [ComputerNumber], [PatientPhone], [InterviewerNumber], [Discharge/ServiceDate], [Message], [Sent], [NotReallySent]) "
        End If
        strSQL += "VALUES ('"
        strSQL += mstrDBName & "', '" & CStr(cboProject.SelectedItem) & "', '" & txtFrom.Text & "', '"
        strSQL += txtSubject.Text & "', "
        strSQL += CInt(txtSurNum.Text) & ", '"
        strSQL += CStr(cboMonth.SelectedItem) & "', "
        strSQL += CInt(txtPatNum.Text) & ", '"
        strSQL += txtModName.Text & "', '" & txtPatName.Text & "', "
        strSQL += CInt(txtCompNum.Text) & ", '"
        strSQL += txtPatPhone.Text & "', "
        strSQL += CInt(txtInterviewerNum.Text) & ", "
        If blnIsCAS Then
            strSQL += "'" & txtMessage.Text & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        Else
            strSQL += "#" & CDate(txtSvcDate.Text) & "#, '"
            strSQL += txtMessage.Text & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        End If
 
        cn = New OleDbConnection(mstrCn)
        cmd = New OleDbCommand(strSQL, cn)
        strErrMsg = "Please try Send again."
 
        Try
            cn.Open()
            cmd.ExecuteNonQuery()   'save data
 
                Catch ex As Exception
            MsgBox(strErrMsg & vbCrLf & ex.message, MsgBoxStyle.Information, "Error Saving Data")
            SaveData = False
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            If SaveData = True Then
                mblnDataChanged = False
            End If
        End Try
    End Function

Thanks so much guys. Your help was greatly appreciated. Hopefully I can return the favor in the future.
The following code simple enough worked well.

Public Sub InsertData()
commandString1 = "insert into Lineinfo(jobnum,linenum) values (123456,3)"
myCommand = New System.Data.SqlClient.SqlCommand
myCommand.Connection = myConnection
myCommand.CommandText = commandString1
myCommand.CommandType = CommandType.Text
myCommand.ExecuteNonQuery()
End sub

Couldn't have gotten there without each and everyone of your suggestions.

Thanks again,

Tomson

hurrah!

glad we could help. :D