Insert Query Not Working?

[seano]

HI all my Insert query isnt working, i have all my database code in one class and i have called the insert query before and it worked fine, here is the insert function

clsDatamanagement this function is setup so all i have to pass it is the all the values to insert except the ID of the table im inserting into:

Public Sub Insert(ByVal tableName As String, ByVal insertClause As String, ByVal ID As String)
        Try
            Dim myOleDbConnection As New OleDbConnection(connectionString)
            ' Get the result as a Table - one record per object
            myOleDbConnection.Open()
            Dim myCommand As New OleDbCommand("INSERT INTO " & tableName & " VALUES (" & getNextID(tableName, ID) & ", " & insertClause, myOleDbConnection)
            myCommand.ExecuteNonQuery()
            myOleDbConnection.Close()
            MsgBox("Saved Successfully!", MsgBoxStyle.Information, "POS System")
        Catch ex As Exception
            MsgBox("Saved UnSuccessfully!", MsgBoxStyle.Critical, "POS System")
        End Try

    End Sub

this getNextID function finds the next available ID in the table:

Public Function getNextID(ByVal tableName As String, ByVal ID As String) As Integer
        'get nextid
        Dim myOleDbConnection As New OleDbConnection(connectionString)
        Dim myOleDbDataAdapter As New OleDbDataAdapter("SELECT " & ID & " FROM " & tableName, myOleDbConnection)
        Dim DSObject As New DataSet
        'create and open the data connection
        myOleDbConnection.Open()

        'Put everything from Data Adapter into DATA SET 
        myOleDbDataAdapter.Fill(DSObject, "Employees")

        Dim NextID As Integer = DSObject.Tables(0).Rows(DSObject.Tables(0).Rows.Count - 1).Item(0)

        Return NextID + 1
    End Function

And Finally the Form code:

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Dim DSUser As New DataSet

        DSUser = DataManagement.SelectObjects("Employees", "Where FirstName= " & "'" & txtname.Text & "'")

        Dim ID As Integer = -1
        If DSUser.Tables(0).Rows.Count = 1 Then
            ID = DSUser.Tables(0).Rows(0).Item("EmployeeID")
        End If
        If ID = -1 Then
            MsgBox("Cant Find Employee", MsgBoxStyle.Critical, "POS System")
        Else
            DataManagement.Insert("TimeSheet", ID & ", '" &  datatimepicker1.Value & "', '" & txthours.Text & "'", "TimeSheetID")

        End If

  End Sub

I had a similar problem with my update query and it was an simple fix, this should be a simple mistake somewhere but i cant get my finger on it, any help would be much appreciated.

I hope i have provided you with enough information for a quick solution, thanks for your time.

 

[IanRyder]

Hi,

The basic format of an insert statement is as follows:-

INSERT INTO <TableName> (<Field1>, <Field2>, <Field_etc>) VALUES (<Value1>,<Value2>,<Value_etc>)

It seems that your inset statement does not follow this format, you have not defined the recipient fields, but it is difficult to say for sure since you have not posted an example of the string variables tableName and insertClause.

Please post examples of the string variables tableName and insertClause if you are still stuck after this.

Cheers,

Ian

 

[jmcilhinney]

It's OK, although I'd say still a bad idea, to omit the column list in an INSERT statement AS LONG AS you provide a value for every column. The values are simply mapped to the columns one by one in the order they appear in the database. If you aren't inserting all columns then that's one issue.

Another potential issue is the fact that you're using string concatenation to build your SQL code and possibly doing that wrong. The way that you've set up your data access code is rather horrible because it precludes you from using parameters, which is the proper way to insert values into SQL code. I strongly suggest that you change your code to use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

In relation to that, it may just be that this:

DataManagement.Insert("TimeSheet", [B][U]ID & ", '" &  datatimepicker1.Value & "', '" & txthours.Text & "'"[/U][/B], "TimeSheetID")

is producing invalid SQL code. Maybe it would be helpful if you told us what actually happens, rather than just that it isn't working. If an exception is thrown then there's an error message and keeping that from us can only be counter-productive. If there's no exception then the behaviour differs from the expected, so you need to describe each to us. That way we have a much better idea of one of the many things that can go wrong to look for.

 

[seano]

It's OK, although I'd say still a bad idea, to omit the column list in an INSERT statement AS LONG AS you provide a value for every column. The values are simply mapped to the columns one by one in the order they appear in the database. If you aren't inserting all columns then that's one issue.

Another potential issue is the fact that you're using string concatenation to build your SQL code and possibly doing that wrong. The way that you've set up your data access code is rather horrible because it precludes you from using parameters, which is the proper way to insert values into SQL code. I strongly suggest that you change your code to use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

In relation to that, it may just be that this:

DataManagement.Insert("TimeSheet", [B][U]ID & ", '" &  datatimepicker1.Value & "', '" & txthours.Text & "'"[/U][/B], "TimeSheetID")

is producing invalid SQL code. Maybe it would be helpful if you told us what actually happens, rather than just that it isn't working. If an exception is thrown then there's an error message and keeping that from us can only be counter-productive. If there's no exception then the behaviour differs from the expected, so you need to describe each to us. That way we have a much better idea of one of the many things that can go wrong to look for.

It does throw an exception "Syntax Error In INSERT INTO Statement", i will look into re designing my function to take parameterized queries, thanks for the quick reply.
EDIT: also i am inserting in all columns.

 

[jmcilhinney]

Have you looked at the actual SQL code you've constructed? The issue might be obvious. If not then you could show it to us and it may be obvious to us. Using parameters will help prevent issues with the values and you might also consider escaping your identifiers, e.g. table and column names, to protect against special characters and reserved words. You wrap them in brackets [] for Microsoft databases and probably graves `` for others.

 

[seano]

Have you looked at the actual SQL code you've constructed? The issue might be obvious. If not then you could show it to us and it may be obvious to us. Using parameters will help prevent issues with the values and you might also consider escaping your identifiers, e.g. table and column names, to protect against special characters and reserved words. You wrap them in brackets [] for Microsoft databases and probably graves `` for others.

with running parametized querys should i put the data from the text boxes into a collection and then send them to the function needed in clsdatamangement or is there a more efficient way of doing it?

 

[jmcilhinney]

I'd probably suggest an Object array. You could probably declare the last parameter of your method as a ParamArray and then you can send them as a single array or as discrete values. Make sure that all values are the correct type, e.g. send dates as DateTime values and not Strings, and then use AddWithValue to add the parameters. If you want to be able to specify the names then you could use a Dictionary but I'm not sure that that serves any purpose here.

 

[seano]

after re writing my code on a whiteboard i finally found the problem i was missing a ")" at the end of the query, i cant believe all this over a missing bracket ahah thanks alot guys:semi-twins:

 

[IanRyder]

Hi,

I have just finished writing this so I thought I would post anyway. I was going to suggest a structure as an alternative. See below an example using a structure.

Imports System.Data.OleDb
 
Public Class Form1
  Public Structure MyRecord
    Dim strCompanyName As String
    Dim strContactName As String
    Dim strValue_etc As String
  End Structure
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim myRec As New MyRecord
    With myRec
      .strCompanyName = "My Company" 'i.e textbox1.text
      .strContactName = "My Contact Name" 'i.e textbox2.text
      .strValue_etc = "Other Stuff" 'other textboxs etc
    End With
    Insert("MyTable", myRec)
  End Sub
 
  Private Sub Insert(ByVal TableName As String, ByVal myRecToSave As MyRecord)
    Dim myOleDbConnection As New OleDbConnection("YourConnectionString")
    Try
      myOleDbConnection.Open()
      Dim myCommand As New OleDbCommand("INSERT INTO @TableName (Field1, Field2, Field_etc) VALUES (@Value1,@Value2,@Value3_etc)", myOleDbConnection)
      With myCommand
        .Parameters.AddWithValue("@TableName", TableName)
        .Parameters.AddWithValue("@Value1", myRecToSave.strCompanyName)
        .Parameters.AddWithValue("@Value2", myRecToSave.strContactName)
        .Parameters.AddWithValue("@Value3_etc", myRecToSave.strValue_etc)
      End With
      myCommand.ExecuteNonQuery()
    Catch ex As Exception
      MsgBox("Saved UnSuccessfully!", MsgBoxStyle.Critical, "POS System")
    Finally
      If myOleDbConnection.State = ConnectionState.Open Then
        myOleDbConnection.Close()
      End If
    End Try
  End Sub
End Class

Cheers,

Ian

 

[seano]

thanks Ian, will this work for other tables? because they all have different field names and column amounts, all i would have to do is change the Structure and the .Parameters.AddWithValue()? im trying to accopilsh one insert function for all tables i.e Timesheet, Employees, Orders and so on.

 

[IanRyder]

Hi,

In principal, Yes. You would need to remove the field names in the SQL string as discussed previously and then ensure that all fields are accommodated for as highlighted by jmcilhinney earlier. You would need to do this using some sort of conditional statements to accommodate the change in structures and the number of parameters that need to be added.

In addition to this, just check that @TableName can be accepted as a parameter. I have never actually passed a table name as a parameter myself.

Otherwise you should be able to achieve this quite easily.

In contrast to the above, rather than make things more complicated for yourself you may find it a lot easier to write a separate insert function for each table.

Cheers,

Ian