Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   Visual Basic Insert Query into SQL (http://www.go4expert.com/forums/visual-basic-insert-query-sql-t25126/)

bouki 3Mar2011 07:35

Visual Basic Insert Query into SQL
 
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 3Mar2011 09:15

Re: Visual Basic Insert Query into SQL
 
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 3Mar2011 11:36

Re: Visual Basic Insert Query into SQL
 
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 3Mar2011 12:04

Re: Visual Basic Insert Query into SQL
 
Quote:

Originally Posted by bouki (Post 80192)
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 3Mar2011 12:18

Re: Visual Basic Insert Query into SQL
 
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 3Mar2011 12:50

Re: Visual Basic Insert Query into SQL
 
Quote:

Originally Posted by bouki (Post 80198)
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 3Mar2011 18:42

Re: Visual Basic Insert Query into SQL
 
Quote:

Originally Posted by shyam_oec (Post 80202)
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 3Mar2011 19:03

Re: Visual Basic Insert Query into SQL
 
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 5Mar2011 20:21

Re: Visual Basic Insert Query into SQL
 
Quote:

Originally Posted by bouki (Post 80225)
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.


All times are GMT +5.5. The time now is 18:54.