Question Inserting into a database using MySQL

[tjpowers]

Hey everyone.
Freshly out of college from a programming course. To be honest, I was not able to learn everything as much as others, I learn much better by experimenting and such, and I find that I am getting good in VB right now. I am using VS 2008, with MySQL connector and WAMP as a means of going into my database. After writing my entire code, I keep getting errors on Inserting data from an unbound datagridview from my form onto my tableadapter and into the database itself. Below is the code, I would highly appreciate the assistance, as I am doing this side job at work to try to impress the boss thanks a bunch guys! I willd o my best to help others as well.

        Dim MySQLConnection As New MySqlConnection("server=localhost;User Id=webuser;Persist Security Info=True;database=time manager;password=webuser")


        'Create Command object
        Dim NonQueryCommand As MySqlCommand = MySQLConnection.CreateCommand()


        Try




            MySQLConnection.Open()
            NonQueryCommand.CommandText = _
            "INSERT INTO info(Date, StartTime, EndTime, TimeTaken, CompanyName, ContactName, Description, Chargeable, Rate, TotalCharges)" + _
            "VALUES(@Date, @StartTime, @EndTime, @TimeTaken, @Companyname, @ContactName, @Description, @Chargeable, @Rate, @TotalCharges)"


            NonQueryCommand.Parameters.AddWithValue("@Date", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@StartTime", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@EndTime", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@TimeTaken", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@CompanyName", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@ContactName", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@Description", MySqlDbType.Text)
            NonQueryCommand.Parameters.AddWithValue("@Chargeable", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@Rate", MySqlDbType.VarChar)
            NonQueryCommand.Parameters.AddWithValue("@TotalCharges", MySqlDbType.VarChar)


            NonQueryCommand.Prepare()


            For Each row As DataGridViewRow In DailyReportGrid.Rows
                NonQueryCommand.Parameters("@Date").Value = row.Cells("DateColumn").Value
                NonQueryCommand.Parameters("@StartTime").Value = row.Cells("StartTimeColumn").Value
                NonQueryCommand.Parameters("@EndTime").Value = row.Cells("EndTimeColumn").Value
                NonQueryCommand.Parameters("@TimeTaken").Value = row.Cells("TimeTakenColumn").Value
                NonQueryCommand.Parameters("@CompanyName").Value = row.Cells("CompanyNameColumn").Value
                NonQueryCommand.Parameters("@ContactName").Value = row.Cells("ContactNameColumn").Value
                NonQueryCommand.Parameters("@Description").Value = row.Cells("DescriptionColumn").Value
                NonQueryCommand.Parameters("@Chargeable").Value = row.Cells("ChargeableColumn").Value
                NonQueryCommand.Parameters("@Rate").Value = row.Cells("RateColumn").Value
                NonQueryCommand.Parameters("@TotalCharges").Value = row.Cells("TotalChargesColumn").Value


            Next


            NonQueryCommand.ExecuteNonQuery()


        Catch ex As MySqlException
            MessageBox.Show(ex.ToString)
        Finally
            MySQLConnection.Close()


        End Try

 

[jmcilhinney]

First up, whay are you not binding the data to the grid? You're making your life more difficult for no good reason. Apart from that, it would be useful to know what the error message is and where it occurs. The IDE gives you that information to help you diagnose the issue. If you'd like us to diagnose the issue, you should pass on all the information that you can.

If I had to guess, I'd say that you are getting a syntax error in your SQL code. I don't really use MySQL so I'm not familiar with its keywords but Date is a common keyword in databases so you can't use it as an identifier without escaping it. If that is the case, again, I'm not familiar with how Connector/Net escapes identifiers but it's likely to be either or both of `Date` and [Date]. Note that the first one uses the grave character (pronounced grarv, not grayv), which is on the same key as the tilde (~).

 

[tjpowers]

Thanks for the quick response. the software i am building i do not want to bind to the database, because i do not want it to go from the database to the grid. the software is a call information, placing the date, start and end times, the customer info and charges. once they are filled in textbox, it is saved into the grid. once the program is to be closed, or clicked on print, it will generate the info from the grid to the database. then the grid is emptied.

as far as the error, u are right that it is an SQL syntax error, or more precise, {"Input string was not in a correct format."}
so used to using phpmyadmin and sql builders that i dont know how to perfectly write decent mysql... used to oracle, and some pl sql... they are all similar but yet completely different.

 

[jmcilhinney]

That's not a reason to not use binding. Unlike VB6 and ADO, there's no direct connection to the database in .NET data-binding. You bind your controls to some list that is stored in memory. In your case, that would most likely be a DataTable. You can build the schema of that DataTable based on your database schema, bind it to the grid, populate it via the grid and then use a DataAdapter to save the data back to the database in a batch. You don't have to use data-binding but it will simplify things and I can't see a reason not to.

Apart from that, why are all your columns type varchar? You've got dates and numbers in there there, so why are you storing everything as text?

 

[Robert_SF]

tj, nobody learns programming just by reading a book, so you're on the right track with experimenting.

Ok, if it's an SQL syntax error, then you know it's nothing about your vb code. Your SQL string seems chopped off at the end, so I can't tell, but a common mistake is to forget to put a space between two segments of SQL string. For example:

SQLstring = "SELECT name, phone, whatever" _
            "FROM customers"

That's not going to work.

Why don't you use the debugger to pause the program and see what actually winds up in the CommandText?

 

[tjpowers]

Tried that... the command line goes threw fine. Its when it arrives at line 50 (executenonquery), thats when it starts sending to the database itself.

and jmcilhinney, the reason they are all in varchar right now, is because it was, in my mind simpler to send everything in the database as strings, because believe it or not, i didnt use to have problems with strings before, but dates were a major pain. the last two variables should be decimal, but if i send anything with a decimal, it truncates the value to .0 for some reason. the database itself does that, and i havent figured out why. some of this stuff i never learned in college, especially with the mysql database, because sql server iss upposed to be oh-so-much-better... but it has a damn wizard rather than having to write all of the sql.

i digress... the issue remains that it doesnt accept strings at all. i thought it was due to the quoatation marks. its not the variable
if i send for example ?Description, inside the Description should be "testing"; and it says its invalid input. didnt think there were more wyas than one to do a string ... ugh...

 

[jmcilhinney]

You really have made this way more difficult for yourself than it needs to be. Define all columns in the database as the types they should be for the data they need to store and use data-binding.

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SQL query here", connection)
Private builder As New SqlCommandBuilder(adapter)
Private table As New DataTable

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    adapter.FillSchema(table, SchemaType.Source)
    DataGridView1.DataSource = table
End Sub

Private Sub AddNewRow(text As String, aDate As Date, number As Decimal)
    table.Rows.Add(text, aDate, number)
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    adapter.Update(table)
End Sub

That's basically it. The DataAdapter builds the schema in the DataTable based on a query but no data is actually retrieved. The columns are added to the grid automatically courtesy of data-binding. The CommandBuilder creates the InsertCommand for you, parameters and all.

Note that I have used SqlClient there but the code is exactly the same for MySqlClient with just the types changed.