none
guys help my code is failing to run its showing syntax error in insert into statement when i debug RRS feed

  • Question

  • Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

            provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
            dataFile = "C:\Users\achikanya\Desktop\xray database.accdb"
            connString = provider & dataFile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Dim str As String
            str = "Insert into Patient Details([National ID],[First Name],[Last Name],[Phone number],[Email],[Procedure],[Gender],[Address],[Date]) Values (?????????))"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("National ID", CType(TextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("First Name", CType(TextBox2.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Last Name", CType(TextBox3.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Phone number", CType(TextBox4.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("(Email", CType(TextBox5.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Procedure", CType(TextBox6.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Gender", CType(TextBox7.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Address", CType(TextBox8.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Date", CType(TextBox9.Text, String)))
    Wednesday, October 31, 2018 3:01 PM

All replies

  • Change your Values from (??) to (@Val1,@Val2) etc

    then change your param calls to

    cmd.Parameters.AddWithValue("@Val1",Texbox1.Text) 'Etc

    Also you dont need to cast a string to a string


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, October 31, 2018 3:09 PM
  • Hello,

    MS-Access has positional parameters meaning that the question marks are fine but need to be comma separatered

    str = "Insert into Patient Details([National ID],[First Name],[Last Name],[Phone number],[Email],[Procedure],[Gender],[Address],[Date]) Values (?,?,?,?,?,?,?,?,?))"

    Since parameters are positional/ordinal when adding parameters with 

    cmd.Parameters.Add(New OleDbParameter

    They must be in the proper order as with the field list in the INSERT INTO statement.

    On a side note, if you were dealing with SQL-Server parameters are no ordinal by default but instead named parameters so you would not use ? but instead for FirstName @FirstName. Oracle is positional by default even if you used named parameters unless you indicate by a property for the command object.

    Lastly, you should not use spaces in field names as when bringing data back you can so say

    SELECT FirstName As [First Name] FROM Customers or if showing in a control e.g. label or DataGridView you can do formatting there.

    On a similar note, avoid naming a field "Date" as per this page.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, October 31, 2018 5:15 PM
    Moderator
  • Also try adding “[ ]”: INSERT INTO [Patient Details] . . .


    Wednesday, October 31, 2018 8:07 PM
  • Also try adding “[ ]”: INSERT INTO [Patient Details] . . .


    Dang, that slipped right by me. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, October 31, 2018 9:04 PM
    Moderator
  • Also try adding “[ ]”: INSERT INTO [Patient Details] . . .


    Dang, that slipped right by me. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    I missed that too, probably because the entire statement made my eyes bleed

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, November 1, 2018 12:45 PM
  • Also try adding “[ ]”: INSERT INTO [Patient Details] . . .


    Dang, that slipped right by me. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    I missed that too, probably because the entire statement made my eyes bleed

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Or we are use to seeing table names without spaces and gravitate to the other parts of the statement, at least that is what I did.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 1, 2018 1:13 PM
    Moderator