Wrong Data being sent to Mysql database from VB.net through paramatized insert statement

Question

I am trying to insert data to mysql from vb.net. When I use concatenated sql query, it works well but when I make it paramatized, no error is shown by the vb.net try and catch statement, but wrong values and some null values are sent.

MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=127.0.0.1;userid=root;password=root;database=my_mysql_database"
Dim READER As MySqlDataReader
Try
    MysqlConn.Open()
    Dim Query As String
    'Query = "INSERT INTO my_mysql_database.edata(E_id,Name,Surname,Age,user_name,password, Gender)values('" & Txt_EID.Text & "', '" & Txt_Name.Text & "', '" & Txt_Surname.Text & "', '" & Txt_Age.Text & "', '" & Txt_User_Name.Text & "', '" & Txt_Password.Text & "', '" & Txt_Gender.Text & "')"'
    Query = "INSERT INTO my_mysql_database.edata 
             values(E_id=@Eid, Name=@Name, Surname=@Surname, Age=@Age,
             user_name=@UserName, password=@Pass, Gender=@Gender, DOB=@Dob,
             Image=@Ima, Email=@Email)"
    COMMAND = New MySqlCommand(Query, MysqlConn)
    COMMAND.Parameters.AddWithValue("@Eid", Txt_EID.Text)
    COMMAND.Parameters.AddWithValue("@Name", Txt_Name.Text)
    COMMAND.Parameters.AddWithValue("@Surname", Txt_Surname.Text)
    COMMAND.Parameters.AddWithValue("@Age", Txt_Age.Text)
    COMMAND.Parameters.AddWithValue("@UserName", Txt_User_Name.Text)
    COMMAND.Parameters.AddWithValue("@Pass", Txt_Password.Text)
    COMMAND.Parameters.AddWithValue("@Gender", Txt_Gender.Text)
    COMMAND.Parameters.AddWithValue("@Dob", DTP_Date.Value)
    COMMAND.Parameters.AddWithValue("@Ima", Txt_Image.Text)
    COMMAND.Parameters.AddWithValue("@Email", Txt_Email.Text)
    READER = COMMAND.ExecuteReader
    MsgBox("Added to Database")
    MysqlConn.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    MysqlConn.Dispose()
End Try

When I do the paramatized way no error is shown, but a zero and all nulls are saved in mysql database. enter image description here

During Debug I have checked the values stored in the parameters and they are correct, but what is sent to Mysql dataabse is a zero and all NULLS. What could be happening. How can Isend the correct values to Mysql Database

edit: Here is a picture of datatypes enter image description here

As for the image column in the database, I have set it as a varchar as I am making this is just to learn for now. — Ahmed Faizan
– Ahmed Faizan, Commented Apr 16, 2014 at 11:36
Are all these fields varchar fields or do you have some numerics (E_id. Age) ? — Steve
– Steve, Commented Apr 16, 2014 at 11:52
Thank you for the response. Yes They are numerics, But when I use the concatenated version there is no problem. — Ahmed Faizan
– Ahmed Faizan, Commented Apr 16, 2014 at 11:54
Well, try to put a Convert.ToInt32(textbox) around the parameter value. AddWithValue creates a parameter whose datatype is derived from the value passed. You pass strings and the parameter datatype will be a string — Steve
– Steve, Commented Apr 16, 2014 at 11:58

WozzeC · Accepted Answer · 2014-04-16 12:10:36Z

2

I might be off here, if that is the case someone can educate me. My SQL knowledge tells me your query is wrong.

You say that this one work:

Query = "INSERT INTO my_mysql_database.edata(E_id,Name,Surname,Age,user_name,password, Gender)values('" & Txt_EID.Text & "', '" & Txt_Name.Text & "', '" & Txt_Surname.Text & "', '" & Txt_Age.Text & "', '" & Txt_User_Name.Text & "', '" & Txt_Password.Text & "', '" & Txt_Gender.Text & "')"'

So why change the format of the second query? INSERT INTO Table Values (Column=Value) Surely it is supposed to be INSERT INTO Table (Column) Values (Value). Hence your query should look like this:

Query = "INSERT INTO my_mysql_database.edata (E_id,Name,Surname,Age,user_name,password,Gender,DOB,Image,Email) Values (@Eid,@Name,@Surname,@Age,@UserName,@Pass,@Gender,@Dob,@Ima,@Email)

Also, I strongly suggest you setup E_id to be auto incremental. Adding the ID yourself seems a bit unecessary.

edited Apr 16, 2014 at 12:10

answered Apr 16, 2014 at 11:58

WozzeC

2,6601 gold badge16 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

WozzeC Over a year ago

Do you mean the one that says SET (column=value)? Because his query says VALUES. Oh the other post disapeared :D

Steve Over a year ago

Yes, that's probably the error, missing SET before the first value

Ahmed Faizan Over a year ago

Yes it was. Thanks to both for trying PS the parmatized answer of Wozzec is missing the @Ima here is the one that works Query = "INSERT INTO my_mysql_database.edata (E_id,Name,Surname,Age,user_name,password,Gender,DOB,Image,Email) values (@Eid,@Name,@Surname,@Age,@UserName,@Pass,@Gender,@Dob,@Ima,@Email)"

WozzeC Over a year ago

Ah, wops :D Glad it worked out for you. I'll update the post.

Ahmed Faizan Over a year ago

I am trying to so Update statement but an error is shown any Idea on this one Query = "UPDATE my_mysql_database.edata SET(E_id=@Eid, Name=@Name, Surname=@Surname, Age=@Age, user_name=@UserName, password=@Pass, Gender=@Gender, DOB=@Dob, Image=@Ima, Email=@Email) where E_id='" & Txt_EID.Text & "'"

|

Collectives™ on Stack Overflow

Wrong Data being sent to Mysql database from VB.net through paramatized insert statement

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related