problems with insert sintax

Printable View

October 24th, 2008, 08:18 AM
mosu

problems with insert sintax

i have an error with the following sintax (i used it in VB.NET) ..really don't know why..it says it's not correct i think

Code:

cmd.CommandText = "INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES ('" & row("pid") & "','" & row("team") & "','" & row("name") & "','" & row("nr") & "','" & row("position") & "','" & country_id & "','" & row("face") & "','" & row("hair") & "','" & row("boot") & "')"

AND HERE EXACTLY WITH VALUES

Code:

cmd.CommandText = "INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES ('52862','141','M. Matei','23','CF','20','da','da','nu')"
October 24th, 2008, 08:33 AM
mmetzger

Re: problems with insert sintax

What is the error you receive?
October 25th, 2008, 08:31 PM
eclipsed4utoo

Re: problems with insert sintax

I can only assume that your ids(idPlayer, idTeam, of_id) and "number" are integers. You are inserting them as strings.
October 27th, 2008, 02:05 AM
vuyiswam

Re: problems with insert sintax

Really i have never seen something like this.

Code:

VALUES ('" & row("pid") & "','" & row("team") & "','" & row("name") & "','" & row("nr") & "', '" & row("position") & "','" & country_id & "','" & row("face") & "','" & row("hair") & "','" & row("boot") & "')"

why are you doing this to SQL ? , Are you using OLE or SQL ?

If you are Using SQL why dont you use a StoredProcedure? If its Ole i stall dont understant the above part. remove the quotes , remove the "&" Sign you dont need that , you just making your life miserable.

TEll me if you understood what i said.

Thank you
October 27th, 2008, 07:18 AM
eclipsed4utoo

Re: problems with insert sintax

Quote:

Originally Posted by vuyiswam

Really i have never seen something like this.

Code:

VALUES ('" & row("pid") & "','" & row("team") & "','" & row("name") & "','" & row("nr") & "', '" & row("position") & "','" & country_id & "','" & row("face") & "','" & row("hair") & "','" & row("boot") & "')"

why are you doing this to SQL ? , Are you using OLE or SQL ?

If you are Using SQL why dont you use a StoredProcedure? If its Ole i stall dont understant the above part. remove the quotes , remove the "&" Sign you dont need that , you just making your life miserable.

TEll me if you understood what i said.

Thank you

isn't the ampersand used to concatenate strings in VB?
October 27th, 2008, 07:33 AM
vuyiswam

Re: problems with insert sintax

isn't the ampersand used to concatenate strings in VB?

Yes it is but thats Naked SQL
October 27th, 2008, 08:18 PM
mmetzger

Re: problems with insert sintax

No - it's not just SQL - it's concatenating the values of the row into a given ad-hoc SQL statement.
October 28th, 2008, 12:20 AM
vuyiswam

Re: problems with insert sintax

And its not a Good Idea
October 28th, 2008, 03:38 AM
HanneSThEGreaT

Re: problems with insert sintax

Quote:

Originally Posted by vuyiswam

remove the quotes , remove the "&" Sign you dont need that , you just making your life miserable.

What ¿ :confused:

By the looks of it the OP, is using this concatenated string to insert the values into the appropriate table. Perfectly legal in VB.NET.

Quote:

Originally Posted by vuyiswam

And its not a Good Idea

It can be, depending on how the values are joined together.... ;)

I'd actually recommend Parameterized queries here. When it comes to Insert, Update etc. SQL statements, PQs can make your life even much easier, and you actually have more control of the updates, insertions.

My 2 cents....
October 29th, 2008, 01:45 PM
mosu

Re: problems with insert sintax

I used the concatenate string because what I need to get is a string .
I`ll post here all code to understand better. thanks to all who answered me! Something like that worked in other part of my program but don't know why now it stucked, the 2 command_texts are almost same thing

THIS WORKS

Code:

Private Sub add_leagues() My.Computer.FileSystem.CopyFile(TextBox4.Text, Application.StartupPath & "\Images\Badges\Countries\" & country_id & ".png", True) For x As Integer = 1 To divisions.Length My.Computer.FileSystem.CopyFile(TextBox5.Text & "\" & x & ".png", Application.StartupPath & "\Images\Leagues\" & country_id & "-" & x & ".png", True) Next Dim cmd As New Odbc.OdbcCommand() cmd.Connection = oODBCConnection For x As Integer = 0 To ListBox1.Items.Count - 1 Dim id_league As Integer = x + 1 Dim leagueName As String = ListBox1.Items.Item(x).ToString Dim fixtureName As String = leagueName.Replace(" ", "_") Dim teamNUm As Integer = team_number(divisions(x), league_offsets(Array.IndexOf(league_offsets, divisions(x)) + 1)) cmd.CommandText = "INSERT INTO Leagues (idCountry, idLeague,leagueName,teams,fixtureName)VALUES('" & country_id & "','" & id_league & "','" & leagueName & "','" & teamNUm & "','" & fixtureName & "')" cmd.ExecuteNonQuery() Next Dim y As Integer = 1 For x As Integer = 1 To divisions.Length System.IO.Directory.CreateDirectory(Application.StartupPath & "\Images\Badges\" & country.Text & "\" & x) For y = y To team_number(divisions(x - 1), league_offsets(Array.IndexOf(league_offsets, divisions(x - 1)) + 1)) My.Computer.FileSystem.CopyFile(TextBox6.Text & "\" & y & ".png", Application.StartupPath & "\Images\Badges\" & country.Text & "\" & x & "\" & divisions(x) + (y - 1) & ".png") Next Next System.IO.Directory.CreateDirectory(Application.StartupPath & "\Images\Tickets\" & country_id) My.Computer.FileSystem.CopyFile(TextBox8.Text, Application.StartupPath & "\Images\Tickets\" & country_id & "\ticket.jpg") 'CROP IMAGE CODE HERE End Sub

THIS DOESN'T WORK

Code:

Private Sub add_teams() Dim cmd As New Odbc.OdbcCommand() cmd.Connection = oODBCConnection For Each row As DataRowView In dtTeam.DefaultView cmd.CommandText = "INSERT INTO Teams (idTeam,teamName,idLeague,idDivision,ksName,pictureBadges)VALUES('" & row("idx") & "','" & row("name") & "','" & country_id & "','" & row("division") & "','" & row("kit_path") & "','Images\Badges\" & country.Text & "\" & row("division") & "\')" Next cmd.ExecuteNonQuery() End Sub

OTHER PARTS OF THE CODE

Code:

Dim oODBCConnection As Odbc.OdbcConnection Dim sConnString As String = _ "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=C:\Documents and Settings\mosu\My Documents\Visual Studio 2005\Projects\League Importer - by Mosu\League Importer - by Mosu\OFDB.mdb;" Private Sub import_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button17.Click oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() If country.Text = "" Then MsgBox("Write a country name") Exit Sub End If read_of(TextBox1.Text) filter_tables() add_country() add_leagues() add_players() add_teams() 'kitserver() 'add_stadiums() oODBCConnection.Close() End Sub
October 29th, 2008, 02:05 PM
eclipsed4utoo

Re: problems with insert sintax

you are inserting your id fields as text fields....which they are most likely not.
October 30th, 2008, 03:42 PM
mosu

Re: problems with insert sintax

so instead 'row["idx"]' i should use directly row["idx"]
October 30th, 2008, 03:58 PM
nabeelisnabeel

Re: problems with insert sintax

Quote:

Originally Posted by mosu

i have an error with the following sintax (i used it in VB.NET) ..really don't know why..it says it's not correct i think

Code:

cmd.CommandText = "INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES ('" & row("pid") & "','" & row("team") & "','" & row("name") & "','" & row("nr") & "','" & row("position") & "','" & country_id & "','" & row("face") & "','" & row("hair") & "','" & row("boot") & "')"

AND HERE EXACTLY WITH VALUES

Code:

cmd.CommandText = "INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES ('52862','141','M. Matei','23','CF','20','da','da','nu')"

Error plz ?
October 31st, 2008, 07:32 AM
eclipsed4utoo

Re: problems with insert sintax

Quote:

Originally Posted by mosu

so instead 'row["idx"]' i should use directly row["idx"]

correct.

so your query would look something like this...

Code:

cmd.CommandText = "INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES (row("pid") & "," & row("team") & ",'" & row("name") & "'," & row("nr") & ",'" & row("position") & "'," & country_id & ",'" & row("face") & "','" & row("hair") & "','" & row("boot") & "')"

that is assuming the "idPlayer", "idTeam", "number", and "of_id" are all defined as some type of numeric value in the database.
November 3rd, 2008, 02:44 AM
mosu

Re: problems with insert sintax

yeah know i understand...first time i thought that i have to use only strings..now i realise i was totally noob :D ..thanks for your help i will test today
November 4th, 2008, 03:31 PM
mosu

Re: problems with insert sintax

still doesn't work, i receive Syntax Error

Code:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Syntax executed :

Code:

"INSERT INTO Players (idPlayer,idTeam,playerName,number,position,of_id,face,hair,boot)VALUES (52862,141,'M. Matei',23,'CF',23,'fals','fals','fals')"

I must say you that in the DB i have more fields than i used in the syntax( i have ID (auto-number) and "available" as true/false field.)
last 3 fields used in the syntax (face , hair, boot) are declared as text in database
November 4th, 2008, 03:41 PM
TheCPUWizard

Re: problems with insert sintax

Open Microsoft Access. PASTE (not type) the QUERY into the SQL view.

1) Does it Run?
2) Did the Designer change the Syntax?
November 4th, 2008, 05:33 PM
mosu

Re: problems with insert sintax

lol the error was because column "number". i changed the name(because access had sth wrong with this) and now i will try in VB, but i think it works now