What is the codes of unsaving data in mysql for failed connection

Question

1.00/5 (2 votes)

See more:

My codes are working but when I checked my database in mysql it's recorded . And when I run my program and shows my data in datagridview inside my form it shows an error "failed to convert system/time..." Can somebody help me with this ? :( I'm badly need your help for my project.

VB

Dim cn As New MySqlConnection
       Dim cmd As New MySqlCommand
       Dim dr As MySqlDataReader
       cn.ConnectionString = "Server = localhost;user id =root;password=;database = dbmagbanua"
       cmd.Connection = cn

Try
       cn.Open()
       cmd.CommandText = "SELECT patient_no FROM tblinfo WHERE patient_no = '" & txtpatientno.Text & "' "
       dr = cmd.ExecuteReader()

       If dr.HasRows Then
           MsgBox(" ID Number already exist", MsgBoxStyle.Critical, "Checkpoint")
           txtpatientno.Clear()
           txtlname.Clear()
           txtfname.Clear()
           txtmid.Clear()
           txtbday.Clear()
           txtgender.Clear()
           txtphoneno.Clear()
           txtaddress.Clear()
           txtlast.Clear()
           txtstart.Clear()
           txtvisit.Clear()
           txtmed.Clear()
           txtpast.Clear()



       Else
           cmd.Dispose()
           dr.Dispose()
           cmd.CommandText = "Insert into tblinfo(patient_no,last_name,first_name,middle_initial,birthdate,gender,phone_no,address) VALUES ('" & txtpatientno.Text & "','" & txtlname.Text & "','" & txtfname.Text & "','" & txtmid.Text & "','" & txtbday.Text & "','" & txtgender.Text & "','" & txtphoneno.Text & "','" & txtaddress.Text & "') "
           cmd.ExecuteNonQuery()
           cmd.CommandText = "Insert into tblhistory(patient_no,med_history,procedure_code,last_visit) VALUES ('" & txtpatientno.Text & "','" & txtmed.Text & "','" & txtpast.Text & "','" & txtlast.Text & "') "
           cmd.ExecuteNonQuery()
           cmd.CommandText = "Insert into tblprocedure(patient_no,start,end,visit,procedure_code) VALUES ('" & txtpatientno.Text & "','" & txtstart.Text & "','" & txtend.Text & "','" & txtvisit.Text & "','" & txtprocedure.Text & "') "
           cmd.ExecuteNonQuery()
           MsgBox("Information seccessfully saved!", MsgBoxStyle.Information, "Saving Data Successed!")
           cmd = New MySqlCommand("Select Count(*) AS Total from tblinfo ", cn)
           Dim i As Integer = cmd.ExecuteScalar()
           cmd = Nothing
           Label20.Text = i
           txtpatientno.Clear()
           txtlname.Clear()
           txtfname.Clear()
           txtmid.Clear()
           txtbday.Clear()
           txtgender.Clear()
           txtphoneno.Clear()
           txtaddress.Clear()
           txtlast.Clear()
           txtstart.Clear()
           txtvisit.Clear()
           txtprocedure.Clear()
           txtmed.Clear()
           txtpast.Clear()


           frmpayment.Show()
           Me.Hide()

       End If
 Catch myerror As MySqlException
	MessageBox.Show("Cannot connect to database: " & myerror.Message)
  Finally
	Mysql.conn.Dispose()

       cn.Close()

Posted 25-Sep-15 18:03pm

Meepoxxxx

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2015-09-25T18:52:00

Is this really VB, not VB.NET?
Never hardcode connection string.
Never ever "compose" SQL statements like this. Use parametrized queries or Entity Framework.
See: http://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html[^]
http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-entity-framework-winform-data-source.html[^]

Doing so will eliminate the security risk of SQLi, but also the problems rising from data type representations. Timestamps are rather problematic in this sense.

Wendelius · Answer 2 · 2015-09-25T19:57:00

Solution 2

Most likely the problem occurs because your data operations concatenate the values directly to the SQL statement. This leaves you open to SQL injections and introduces problems with implicit data type conversions.

As for cure, you should use MySqlParameter[^] with all the statements. For example

VB

cmd.CommandText = "Insert into tblinfo(patient_no,last_name,first_name,middle_initial,birthdate,gender,phone_no,address) VALUES (:patient_no, :last_name, :first_name, :middle_initial, :birthdate, :gender, :phone_no, :address) "

cmd.Parameters.AddWithValue("patient_no", txtpatientno.Text)
cmd.Parameters.AddWithValue("last_name", txtlname.Text)
cmd.Parameters.AddWithValue("first_name", txtfname.Text) 
cmd.Parameters.AddWithValue("middle_initial", txtmid.Text)
cmd.Parameters.AddWithValue("birthdate", txtbday.Text)
cmd.Parameters.AddWithValue("gender", txtgender.Text )
cmd.Parameters.AddWithValue("phone_no", txtphoneno.Text )
cmd.Parameters.AddWithValue("address", txtaddress.Text)

cmd.ExecuteNonQuery()

Posted 25-Sep-15 19:57pm

Wendelius

Comments

Meepoxxxx 26-Sep-15 5:18am

Like this ? uh. still won't work.

Meepoxxxx 26-Sep-15 5:20am

Dim cn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim dr As MySqlDataReader

cn.ConnectionString = "Server = localhost;user id =root;password=;database = dbmagbanua"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "SELECT patient_no FROM tblinfo WHERE patient_no = '" & txtpatientno.Text & "' "
dr = cmd.ExecuteReader()

If dr.HasRows Then
MsgBox(" ID Number already exist", MsgBoxStyle.Critical, "Checkpoint")
txtpatientno.Clear()
txtlname.Clear()
txtfname.Clear()
txtmid.Clear()
txtbday.Clear()
txtgender.Clear()
txtphoneno.Clear()
txtaddress.Clear()
txtlast.Clear()
txtstart.Clear()
txtvisit.Clear()
txtmed.Clear()
txtpast.Clear()

Else
cmd.Dispose()
dr.Dispose()
cmd.CommandText = "Insert into tblinfo(patient_no,last_name,first_name,middle_initial,birthdate,gender,phone_no,address) VALUES ('" & txtpatientno.Text & "','" & txtlname.Text & "','" & txtfname.Text & "','" & txtmid.Text & "','" & txtbday.Text & "','" & txtgender.Text & "','" & txtphoneno.Text & "','" & txtaddress.Text & "') "
cmd.Parameters.AddWithValue("patient_no", txtpatientno.Text)
cmd.Parameters.AddWithValue("last_name", txtlname.Text)
cmd.Parameters.AddWithValue("first_name", txtfname.Text)
cmd.Parameters.AddWithValue("middle_initial", txtmid.Text)
cmd.Parameters.AddWithValue("birthdate", txtbday.Text)
cmd.Parameters.AddWithValue("gender", txtgender.Text)
cmd.Parameters.AddWithValue("phone_no", txtphoneno.Text)
cmd.Parameters.AddWithValue("address", txtaddress.Text)

cmd.ExecuteNonQuery()
cmd.CommandText = "Insert into tblhistory(patient_no,med_history,procedure_code,last_visit) VALUES ('" & txtpatientno.Text & "','" & txtmed.Text & "','" & txtpast.Text & "','" & txtlast.Text & "') "
cmd.Parameters.AddWithValue("patient_no", txtpatientno.Text)
cmd.Parameters.AddWithValue("med_history", txtmed.Text)
cmd.Parameters.AddWithValue("procedure_code", txtpast.Text)
cmd.Parameters.AddWithValue("last_visit", txtlast.Text)
cmd.ExecuteNonQuery()
cmd.CommandText = "Insert into tblprocedure(patient_no,start,end,visit,procedure_code) VALUES ('" & txtpatientno.Text & "','" & txtstart.Text & "','" & txtend.Text & "','" & txtvisit.Text & "','" & txtprocedure.Text & "') "
cmd.Parameters.AddWithValue("patient_no", txtpatientno.Text)
cmd.Parameters.AddWithValue("start", txtstart.Text)
cmd.Parameters.AddWithValue("end", txtend.Text)
cmd.Parameters.AddWithValue("procedure_code", txtprocedure.Text)
cmd.ExecuteNonQuery()

cmd.ExecuteNonQuery()
MsgBox("Information seccessfully saved!", MsgBoxStyle.Information, "Saving Data Successed!")
cmd = New MySqlCommand("Select Count(*) AS Total from tblinfo ", cn)
Dim i As Integer = cmd.ExecuteScalar()
cmd = Nothing
Label20.Text = i
txtpatientno.Clear()
txtlname.Clear()
txtfname.Clear()
txtmid.Clear()
txtbday.Clear()
txtgender.Clear()
txtphoneno.Clear()
txtaddress.Clear()
txtlast.Clear()
txtstart.Clear()
txtvisit.Clear()
txtprocedure.Clear()
txtmed.Clear()
txtpast.Clear()

frmpayment.Show()
Me.Hi

Wendelius 26-Sep-15 7:48am

Now yo have added the parameter values to the command but you don't define the parameters in the SQL statements. The statements should be like the one I wrote in the answer:

Insert into tblinfo(patient_no,last_name,first_name,middle_initial,birthdate,gender,phone_no,address) VALUES (:patient_no, :last_name, :first_name, :middle_initial, :birthdate, :gender, :phone_no, :address)

Meepoxxxx 28-Sep-15 2:47am

Thanks Mika. It works now. Big help (y)

Wendelius 28-Sep-15 16:16pm

Glad it works :)

Sergey Alexandrovich Kryukov · Answer 3 · 2015-09-26T00:04:00

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA

What is the codes of unsaving data in mysql for failed connection

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0