Visual Basic Insert Query into SQL

bouki's Avatar, Join Date: Apr 2010
Go4Expert Member
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?
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
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.
bouki's Avatar, Join Date: Apr 2010
Go4Expert Member
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
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
Quote:
Originally Posted by bouki View Post
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
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.
bouki's Avatar, Join Date: Apr 2010
Go4Expert Member
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.
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
Quote:
Originally Posted by bouki View 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.
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...
bouki's Avatar, Join Date: Apr 2010
Go4Expert Member
Quote:
Originally Posted by shyam_oec View Post
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...
Could you give me an example on how to implement that into my code?
bouki's Avatar, Join Date: Apr 2010
Go4Expert Member
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)
)
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
Quote:
Originally Posted by bouki View Post
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)
)
As you wish, if that is your requirement then its okey.