VB.Net insert to access database

Question

I've just started with VB and have become stuck trying to insert data as a new entry in an access database table... I know my SQL is correct but I don't understand how to use the TableAdapter update function

I have

Me.TFaultLogTableAdapter.Adapter.InsertCommand.CommandText = SQL.newJob(staffNo, zone, jobType, 1)

I am pretty sure I'm missing something, I have used the TableAdapter select command in a similar way with no problems

Any help please :)

First, I dont use TableAdapters so I may not be 100% correct. Usually the InsertCommand is some insert SQL with parameters. This tells the adapter how to insert data into the table. IE `INSERT INTO TFaultLog (StaffNo, zone, jobType, BitFlag) VALUES (?,?,?,1)` Then when you want to save, you tell the adapter to UPDATE (or some command I dont know off the top of my head). I dont know what your doing here. Is SQL.NewJob generating some generic SQL like that?? — Steve, Oct 22 '13 at 21:36
Yes Steve newJob is generating the sql - SQL = "INSERT INTO tFaultLog (loggedBy, reportedBy, zone, fault, jobStart, technician) VALUES (" & loggedBy & ", 1, " & zone & " , " & jobType & ", " & technician & ")" — Matt, Oct 22 '13 at 21:55

score 1 · Answer 1 · answered Oct 22 '13 at 22:17

You are not using the right tool for the job. Assuming you are using SQL Server (since you did not specify) you would want something like this:

Using CN As New SqlClient.SqlConnection("Your connection String")
    Using CMD As New SqlClient.SqlCommand("INSERT INTO tFaultLog (loggedBy, reportedBy, zone, fault, jobStart, technician) " & _
            "VALUES(@P1,@P2,@P3,@P4,@P5,@P6)", CN)

        CMD.Parameters.AddWithValue("@P1", loggedBy)
        CMD.Parameters.AddWithValue("@P2", 1)
        CMD.Parameters.AddWithValue("@P3", zone)
        CMD.Parameters.AddWithValue("@P4", 1)
        CMD.Parameters.AddWithValue("@P5", jobType)
        CMD.Parameters.AddWithValue("@P6", technician)

        CN.Open()
        CMD.ExecuteNonQuery()
        CN.Close()
    End Using
End Using

BTW, your SQL had more fields than values. Must have been a mistype.

VB.Net insert to access database

1 Answers1