Group,

I've been working for several hours trying to correctly write to a database file. Here are the parameters:

Database Name: DATADESIGNSOLUTIONS
Table Name: PRINTERMAINT
Column Names: PrinterID (primary Key), PrinterName, PrinterNumber, PrinterLocation, PrinterAddress

I've been working at this two ways: through the Visual Basic code and through SQL Server Management. I'm getting the same errors in both places. The error reads:

Invalid column name 'printName'.
Invalid column name 'printNo'.
Invalid column name 'printLoc'.
Invalid column name 'printAdd'.

Here's the code I've written"

Try
                con.ConnectionString = "Data Source=DON-PC;Initial Catalog=DATADESIGNSOLUTIONS;Integrated Security = True;User ID=DON-PC;Password=be67011;"
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO PRINTERMAINT(PrinterName, PrinterNumber, PrinterLocation, PrinterAddress) " & _
                "VALUES (printName, printNo, printLoc, printAdd)"
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
            End Try

By the way,

INSERT INTO PRINTERMAINT(PrinterName, PrinterNumber, PrinterLocation, PrinterAddress)

doesn't work in SQL Server Management either. It keeps telling me these are invalid column names.

Can someone point out to me what I'm doing wrong?

In advance, thanks for the help.

Don

Missing quotes on your values. When omitted it searched for column names:

INSERT INTO PRINTERMAINT(PrinterName, PrinterNumber, PrinterLocation, PrinterAddress)
VALUES ('printName', 'printNo', 'printLoc', 'printAdd')

I must be getting somewhere. I'm getting a new error message now:

"Error converting data type varchar to numeric"

I thought this may be a problem. But I'm not sure how to fix it. Here's the commands to store it:

Dim printID As Integer
    Dim printName As String
    Dim printNo As Integer
    Dim printLoc As Integer
    Dim printadd As String      

        printName = txbPrinterName.Text
        printNo = Convert.ToInt32(txbPrinterNo.Text)
        printLoc = Convert.ToInt32(txbLoc.Text)
        printadd = txbPrinterAddress.Text

Clearly it doesn't like 'printNo' and 'printLoc'. How should I be writing this?

Thanks again. You've got me back on the right track.

Don

The preferred way is to use parameters. Because you are using MS SQL I will assume you are using SqlClient and not OleDb.

cmd.CommandText = "INSERT INTO PRINTERMAINT(PrinterName, PrinterNumber, PrinterLocation, PrinterAddress) " & _
    "VALUES (@prtName, @prtNo, @prtLoc, @prtAdd)"

cmd.Parameters.AddWithValue("@prtName", printName)
cmd.Parameters.AddWithValue("@prtNo",   printNo)
cmd.Parameters.AddWithValue("@prtLoc",  printLoc)
cmd.Parameters.AddWithValue("@prtAdd",  printAdd)

Edited 3 Years Ago by Reverend Jim

Rev. Jim, You are correct on all counts. This works perfectly! Now I just need to learn how to get it to assign the ID number.

Thanks for your help.

Don

This question has already been answered. Start a new discussion instead.