Visual Basic Insert Query into SQL

Discussion in 'Visual Basic ( VB )' started by bouki, Mar 3, 2011.

  1. bouki

    bouki New Member

    Joined:
    Apr 9, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I am creating a library system for a local library but im relatively new to Visual Basic. However i have mange to reach this far and getting this error when i try to add the data to the fields
    I am getting this error “Incorrect syntax near keyword User”

    Code:
    Imports System.Data.SqlClient
    Public Class adduser
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddUser.Click
            Dim cn As New SqlConnection
            Dim cmd As New SqlCommand
            
            Try
                cn.ConnectionString = " data source = Rayon\Rayon; Initial catalog = Library; uid=sa; password = minnie"
    
                cn.Open()
    
                cmd.Connection = cn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "Insert into User (UserId, FName, MName, LName, Street1, Street2, City, Zone, ContactHome, ContactWork, ContactCell, Email, Gender) values ('" & txtuserid.Text & "', '" & txtFname.Text & "' '" & txtMname.Text & "' '" & txtLname.Text & "' '" & txtStreet1.Text & "' '" & txtStreet2.Text & "' '" & txtCity.Text & "' '" & cbZone.Text & "' '" & txtHome.Text & "' '" & txtWork.Text & "' '" & txtCell.Text & "' '" & txtEmail.Text & "' '" & cbGender.Text & "' )"
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show("Error while insrting record on table..." & ex.Message, "Insert Records")
            Finally
                cn.Close()
            End Try
    
    
    
        End Sub
    
    
    Is it possible for every new user added a unique 'UserId' is added with the Librarian entering one?
     
  2. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    I think you should change your table name.User is a already existing table in Oracle, but i do'nt know about Sql Server, so you can check it by changing the table name.
     
  3. bouki

    bouki New Member

    Joined:
    Apr 9, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I made that adjustment in SQL but im getting this error now

    (Error while inserting on table...Incorrect syntax near 'slov'.)

    slov is a string i entered in the 'txtMname.text' textbox
     
  4. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    You have left comma in all subsequent values.
    Code:
     cmd.CommandText = "Insert into tableName (UserId, FName, MName, LName, Street1, Street2, City, Zone, ContactHome, ContactWork, ContactCell, Email, Gender) values ('" & txtuserid.Text & "', '" & txtFname.Text & "', '" & txtMname.Text & "', '" & txtLname.Text & "','" & txtStreet1.Text & "' ,'" & txtStreet2.Text & "', '" & txtCity.Text & "' ,'" & cbZone.Text & "', '" & txtHome.Text & "' ,'" & txtWork.Text & "', '" & txtCell.Text & "' ,'" & txtEmail.Text & "', '" & cbGender.Text & "' )"
                cmd.ExecuteNonQuery()
    
    
    Try this.
     
  5. bouki

    bouki New Member

    Joined:
    Apr 9, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    Thanks alot, i actually realise that just a minute b4 u had replied.

    I had ask this question in the initial post...

    How can i omit the UserId field in VB and have it increment each time i add a record. I think the program will work more efficient without the librarian has to enter a UserId every time.
     
  6. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    Yes,you are right .That will be more logical.You can use sequence.
    Before inserting data you can find the next sequence number and then instead of user input you can provide that sequence number.And alongwith the sequence number you can also give any prefix notation like PRC00001..PRC00002...etc...
     
  7. bouki

    bouki New Member

    Joined:
    Apr 9, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    Could you give me an example on how to implement that into my code?
     
  8. bouki

    bouki New Member

    Joined:
    Apr 9, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I found a code, you think this is logical
    Code:
    CREATE TABLE Persons
    (
    P_Id int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (P_Id)
    )
    
     
  9. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    As you wish, if that is your requirement then its okey.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice