CODEWITHSUNDEEP
vb.net
Prasad Dixit
Prasad Dixit

Reputation: 31

Error in Insert into statement in vb.net queries

I tried to match my query with earlier posts but not able to resolve my problem.

I have a table user_master where I am importing permission_id and zone_id from other tables as a key. The date is in the short format.

 cmd.CommandText = "INSERT INTO user_master(uname, password, creation_date, creation_time, permission_id, zone_id)" & "VALUES('" & txtuname.Text & "','" & txtupass.Password & "','" & date_gl & "','" & time_gl & "','" & _user_perm & "','" & _zone_id & "')"

cmd.ExecuteNonQuery()

The primary key is autonumber, creation_date/time was executed in other query successfully and permission_id and zone_id are the numbers. Other fields are texts. I tried several permutations and combinations by altering this query (removing quotes etc.) the field count is also the same. Please let me know what parameter i am executing wrong?

same is the case for, 

cmd.CommandText = "UPDATE user_master SET uname = " & """" & txtuname.Text & """" & _
                        " and password = " & """" & txtupass.Password & """" & _
                        " and creation_date = " & """" & date_gl & """" & _
                        " and creation_time = " & """" & time_gl & """" & _
                        " and permission_id = " & _user_perm & _
                        " and zone_id = " & _zone_id & _
                        " WHERE ID = " & _usr_id_temp
                    cmd.ExecuteNonQuery()

I hope to get right help.

thank you, abhimoh

Upvotes: 0

Views: 55

Answers (1)

Steve
Steve

Reputation: 216343

I suppose from a previous answer that you are using MS-Access and OleDb to update your database.
If this is the case then PASSWORD is a reserved keyword and you need to encapsulate it with square bracket. Then use a parameterized query to avoid parsing problems of text, decimals and dates and of course to prevent Sql Injections 

cmd.CommandText = "UPDATE user_master SET uname = ?" & _
                   " and [password] = ?" & _
                   " and creation_date = ?" & _
                   " and creation_time = ?" & _
                   " and permission_id = ?" & _
                   " and zone_id = ? & _
                   " WHERE ID = ?"
cmd.Parameters.AddWithValue("@p1",txtuname.Text)
cmd.Parameters.AddWithValue("@p2",txtupass.Password)
cmd.Parameters.AddWithValue("@p3",date_gl)
cmd.Parameters.AddWithValue("@p4",time_gl)
cmd.Parameters.AddWithValue("@p5",_user_perm)
cmd.Parameters.AddWithValue("@p6",_zone_id)
cmd.Parameters.AddWithValue("@p7",_usr_id_temp)
cmd.ExecuteNonQuery()

Upvotes: 1

Related Questions