Question INSERT INTO syntaxis error

[lutonian]

Hi all,

I do not have to much experience in VB.net. I am doing small application to practice.

In this case I am creating a VB application conneting to a MYSQL Database.

The problem I have is when I try to make a new register into the DDBB, I recieve a syntaxis error.

INSERT INTO Libros(Titulo,Desc) VALUES ('" & Titulo_text.Text & "', '" & Desc_text.Text & "')

However if I use the command into mysql server it works fine.

INSERT INTO Libros(Title,Desc) VALUES ()

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click

If sConnection.State = ConnectionState.Closed Then
sConnection.ConnectionString = "SERVER = my server; USERID = joserodriguezan; PASSWORD = Aberystwyth; DATABASE = Bibloteca;"
sConnection.Open()
End If
Try
strSQL = "INSERT INTO Libros(Title,Desc) VALUES ('" & Title_text.Text & "', '" & Desc_text.Text & "')"
Dim da As New MySqlDataAdapter(strSQL, sConnection)
da.Fill(dset)
Catch ex As Exception
MessageBox.Show("Error")
End Try

Tittle_text.Text = ""
Desc_text.Text = ""
Class_combo.Text = ""
Date_text.Text = ""
Pres_text.Text = ""
Who_text.Text = ""
Title_text.Focus()
list()
End Sub

Any knows how to resolve this problem?
Thank you.

 

[IanRyder]

Hi,

When you declare and instantiate a DataAdapter, you do so with a valid SQL SELECT query and NOT using an SQL Action query.

Hope that helps.

Cheers,

Ian

 

[lutonian]

HI Ian,

Can you give me an example? I am starting in the VB.Net World.

I think you are telling me to change this "strSQL = "INSERT INTO Libros(Title,Desc) VALUES ('" & Title_text.Text & "', '" & Desc_text.Text & "')""

Thank you for you help.

 

[jmcilhinney]

Hi,

When you declare and instantiate a DataAdapter, you do so with a valid SQL SELECT query and NOT using an SQL Action query.

Hope that helps.

Cheers,

Ian

Strictly speaking, it's not invalid to use an INSERT statement in the SelectCommand of a data adapter, it doesn't make sense to do so. It's still not going to cause a syntax error though.

The first thing you need to do is to learn how to use a data adapter properly. If you provide SQL code when you create one then that code is used in the SelectCommand, which is what gets executed when you call Fill to retrieve data. As such, the only thing that makes sense is to provide a SELECT statement. If you want to be able save data as well when you call Update then you need to populate the InsertCommand, UpdatedCommand and DeleteCommand as well. As the names suggest, the SQL code for those should contain an INSERT, UPDATE and DELETE statement respectively. Check out this thread for two examples of using a data adapter - one with a command builder and one without - as well as some other common ADO.NET scenarios:

Retrieving and Saving Data in Databases

You'll note there that all values are inserted into SQL code using parameters and NOT string concatenation, which is what you're doing. That is one of the most common causes of syntax errors, so using parameters may fix the original issue. If not, you should escape all your identifiers, just in case one of them is a reserved word or contains special characters. I believe that MySQL uses `graves` for the purpose, although MySqlClient may also support [brackets]. To have a command builder insert them automatically, you can set the QuotePrefix and QuoteSuffix properties.

 

[lutonian]

Hi Ian,

You were right the problem were the graves, it was working fine with this string.

strSQL = "INSERT INTO `Libros` (`Title`,`Desc`) VALUES ('" & Title_text.Text & "', '" & Desc_text.Text & "')""

Do you think this could be the best method to work with databases?

1. Dim command As New OleDb.OleDbCommand("INSERT INTO Table1 VALUES(@id, @number, @subjectName, @years, @birthYear)", connection)
   
2. 
   
3. ' Add the parameters for the InsertCommand.
   
4. command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
   
5. command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
   
6. command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
   
7. command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
   
8. command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
   
9. 'command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
   
10. 
    

Thank you for your help Ian,

 

[IanRyder]

Hi,

You were right the problem were the graves, it was working fine with this string.

You need to re-direct your thanks to jmcilhinney since it was he who made the comment about the graves.

Do you think this could be the best method to work with databases?

Yes and No.

Yes, for your use of parameters and:-

No, for two reasons:-

1) Even though the structure of your Insert command is correct if these fields match the total fields in the database, I always prefer to specify my fields to be updated within the Insert command. Please note that this is a preference and not a necessity.

2) You are passing a value form your project to the ID field in your DataTable but if this is supposed to be an Identidy Column / Primary Key then this should be handled by the Database and not by your project.

Hope that helps.

Cheers,

Ian