problems with insert sintax

[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

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

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')"

[mmetzger]

What is the error you receive?

[eclipsed4utoo]

I can only assume that your ids(idPlayer, idTeam, of_id) and "number" are integers. You are inserting them as strings.

[vuyiswam]

Really i have never seen something like this.

	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

[eclipsed4utoo]

Really i have never seen something like this.

	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?

[vuyiswam]

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


Yes it is but thats Naked SQL

[mmetzger]

No - it's not just SQL - it's concatenating the values of the row into a given ad-hoc SQL statement.

[vuyiswam]

And its not a Good Idea

[HanneSThEGreaT]

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

What ¿

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.

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....

[mosu]

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

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

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

  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

[eclipsed4utoo]

you are inserting your id fields as text fields....which they are most likely not.

[mosu]

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

[nabeelisnabeel]

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

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

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 ?

[eclipsed4utoo]

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

correct.

so your query would look something like this...

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.

[mosu]

yeah know i understand...first time i thought that i have to use only strings..now i realise i was totally noob ..thanks for your help i will test today