Im Confused With This MySQL Query Issue

[casirus]

this code is saying theres something wrong with the syntax but it worked last way i used it.....

Sub useringo()
        Dim conn As MySqlConnection
 
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=user; password=pess; database=login"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try
        Dim myAdapter As New MySqlDataAdapter
 
        Dim sqlquery As String
        sqlquery = "select * from userlist Where username='" & My.Settings.unam & "'" & "Insert (name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        myCommand.Parameters.AddWithValue("?name", Form4.TextBox1.Text)
        myCommand.Parameters.AddWithValue("?site", Form4.TextBox3.Text)
        myCommand.Parameters.AddWithValue("?message", Form4.TextBox4.Text)
        myCommand.Parameters.AddWithValue("?about", Form4.TextBox5.Text)
        myCommand.Parameters.AddWithValue("?youtube", Form4.TextBox6.Text)
        myCommand.Parameters.AddWithValue("?myspace", Form4.TextBox7.Text)
        myCommand.Parameters.AddWithValue("?email", Form4.TextBox8.Text)
     
        'start query
        myCommand.ExecuteScalar()
        conn.Close()
    End Sub

the below code is causeing me loads of issues i dunno ware i went wrong it looked like it would work in theory but in practice aprrently not

Sub getsinfo()
        Dim conn As MySqlConnection
 
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=username; password=password; database=login"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try
        Dim myAdapter(8) As MySqlDataAdapter
 
        Dim sqlquery As String
        Dim sqlquery2 As String
        Dim sqlquery3 As String
        Dim sqlquery4 As String
        Dim sqlquery5 As String
        Dim sqlquery6 As String
        Dim sqlquery7 As String
 
        sqlquery = "SELECT name FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery2 = "SELECT site FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery3 = "SELECT message FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery4 = "SELECT about FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery5 = "SELECT youtube FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery6 = "SELECT myspace FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery7 = "SELECT email FROM userlist Where Username='" & My.Settings.unam & "'"
        
        Dim myCommand(8) As MySqlCommand
 
        myCommand(0).Connection = conn
        myCommand(1).CommandText = sqlquery
        myCommand(2).CommandText = sqlquery2
        myCommand(3).CommandText = sqlquery3
        myCommand(4).CommandText = sqlquery4
        myCommand(5).CommandText = sqlquery5
        myCommand(6).CommandText = sqlquery6
        myCommand(7).CommandText = sqlquery7
        'start query
        myAdapter(1).SelectCommand = myCommand(0)
        myAdapter(2).SelectCommand = myCommand(1)
        myAdapter(3).SelectCommand = myCommand(2)
        myAdapter(4).SelectCommand = myCommand(3)
        myAdapter(5).SelectCommand = myCommand(4)
        myAdapter(6).SelectCommand = myCommand(5)
        myAdapter(7).SelectCommand = myCommand(6)
        myAdapter(8).SelectCommand = myCommand(7)
        Dim mydata(6) As String
        mydata(0) = myCommand(1).ExecuteScalar
        mydata(1) = myCommand(2).ExecuteScalar
        mydata(2) = myCommand(3).ExecuteScalar
        mydata(3) = myCommand(4).ExecuteScalar
        mydata(4) = myCommand(5).ExecuteScalar
        mydata(5) = myCommand(6).ExecuteScalar
        mydata(6) = myCommand(7).ExecuteScalar
 
        profile.Label1.Text = mydata(0)
        profile.LinkLabel1.Text = mydata(1)
        profile.Label2.Text = mydata(3)
        profile.TextBox1.Text = mydata(4)
        profile.LinkLabel2.Text = mydata(5)
        profile.LinkLabel3.Text = mydata(6)
        profile.Label3.Text = mydata(7)
    End Sub

>.< i really need help i have no idea whats wrong with this

[CVMichael]

You are setting the connection only to myCommand(0), what about the other 7 ?

[techgnome]

1) There is nothing right about this query...

sqlquery = "select * from userlist Where username='" & My.Settings.unam & "'" & "Insert (name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"

You might want to try displaying it in a messagebox or something so you can see the fully formed query, then you might understand why it is syntactically wrong.

2) In addition to what CVM noted, you never set the command text of myCommand(0) either.

In the furure, when you do get errors, it will go a LONG way to helping solve your problems if you post WHAT the error message is.

-tg

[casirus]

ok well the first bit of code

Sub useringo()
        Dim conn As MySqlConnection
 
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=user; password=pess; database=login"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try
        Dim myAdapter As New MySqlDataAdapter
 
        Dim sqlquery As String
        sqlquery = "select * from userlist Where username='" & My.Settings.unam & "'" & "Insert (name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        myCommand.Parameters.AddWithValue("?name", Form4.TextBox1.Text)
        myCommand.Parameters.AddWithValue("?site", Form4.TextBox3.Text)
        myCommand.Parameters.AddWithValue("?message", Form4.TextBox4.Text)
        myCommand.Parameters.AddWithValue("?about", Form4.TextBox5.Text)
        myCommand.Parameters.AddWithValue("?youtube", Form4.TextBox6.Text)
        myCommand.Parameters.AddWithValue("?myspace", Form4.TextBox7.Text)
        myCommand.Parameters.AddWithValue("?email", Form4.TextBox8.Text)
     
        'start query
        myCommand.ExecuteScalar()
        conn.Close()
    End Sub

gives this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Insert (name, site, message, about, youtube, myspace, email) VALUES( 'hack', 'si' at line 1

then the next code

Sub getsinfo()
        Dim conn As MySqlConnection
 
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=username; password=password; database=login"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try
        Dim myAdapter(8) As MySqlDataAdapter
 
        Dim sqlquery As String
        Dim sqlquery2 As String
        Dim sqlquery3 As String
        Dim sqlquery4 As String
        Dim sqlquery5 As String
        Dim sqlquery6 As String
        Dim sqlquery7 As String
 
        sqlquery = "SELECT name FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery2 = "SELECT site FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery3 = "SELECT message FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery4 = "SELECT about FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery5 = "SELECT youtube FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery6 = "SELECT myspace FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery7 = "SELECT email FROM userlist Where Username='" & My.Settings.unam & "'"
        
        Dim myCommand(8) As MySqlCommand
 
        myCommand(0).Connection = conn
        myCommand(1).CommandText = sqlquery
        myCommand(2).CommandText = sqlquery2
        myCommand(3).CommandText = sqlquery3
        myCommand(4).CommandText = sqlquery4
        myCommand(5).CommandText = sqlquery5
        myCommand(6).CommandText = sqlquery6
        myCommand(7).CommandText = sqlquery7
        'start query
        myAdapter(1).SelectCommand = myCommand(0)
        myAdapter(2).SelectCommand = myCommand(1)
        myAdapter(3).SelectCommand = myCommand(2)
        myAdapter(4).SelectCommand = myCommand(3)
        myAdapter(5).SelectCommand = myCommand(4)
        myAdapter(6).SelectCommand = myCommand(5)
        myAdapter(7).SelectCommand = myCommand(6)
        myAdapter(8).SelectCommand = myCommand(7)
        Dim mydata(6) As String
        mydata(0) = myCommand(1).ExecuteScalar
        mydata(1) = myCommand(2).ExecuteScalar
        mydata(2) = myCommand(3).ExecuteScalar
        mydata(3) = myCommand(4).ExecuteScalar
        mydata(4) = myCommand(5).ExecuteScalar
        mydata(5) = myCommand(6).ExecuteScalar
        mydata(6) = myCommand(7).ExecuteScalar
 
        profile.Label1.Text = mydata(0)
        profile.LinkLabel1.Text = mydata(1)
        profile.Label2.Text = mydata(3)
        profile.TextBox1.Text = mydata(4)
        profile.LinkLabel2.Text = mydata(5)
        profile.LinkLabel3.Text = mydata(6)
        profile.Label3.Text = mydata(7)
    End Sub

gives me on this line

myCommand(0).Connection = conn

Object reference not set to an instance of an object.

[techgnome]

regarding your first command..... again... PRINT OUT or DISPLAY your SQL before you try to run it.... MAKE SURE it is correct. I can already tell you that it is NOT. HINT: You have two statements, that have nothing to do with each other... either SELECT or INSERT, not both (at least not the way you have it)

Your second code - before you can use an object you first have to create it. When you created your array, what you get is essentially a carton of eggs... only it's empty. Before you can use any of the eggs, they have to be created.

Dim carton(12) as egg

egg(0).shell = broken 'this will generat an error... the egg doesn't exist yet

...

Dim carton2(12) as egg
carton(0) = new egg   'Here we've created a new egg
carton(0).shell = broken 'NOW we can use it

Neat thing about .NET, you can create your carton and fill it at the same time:

Dim carton(12) as egg = {new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg]

carton(0).shell = broken 'woot! this should work

-tg

[casirus]

Sub getsinfo()
        Dim conn As [CODE]if i put new here it does nothing[/CODE] MySqlConnection
 
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=username; password=password; database=login"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try
        Dim myAdapter(8) As MySqlDataAdapter
 
        Dim sqlquery As String
        Dim sqlquery2 As String
        Dim sqlquery3 As String
        Dim sqlquery4 As String
        Dim sqlquery5 As String
        Dim sqlquery6 As String
        Dim sqlquery7 As String
 
        sqlquery = "SELECT name FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery2 = "SELECT site FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery3 = "SELECT message FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery4 = "SELECT about FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery5 = "SELECT youtube FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery6 = "SELECT myspace FROM userlist Where Username='" & My.Settings.unam & "'"
        sqlquery7 = "SELECT email FROM userlist Where Username='" & My.Settings.unam & "'"
        
      [CODE]  Dim myCommand(8) As New MySqlCommand <<< this line errors saying that Arrays Cannot Be Declared With NEW[/CODE]
 
        myCommand(0).Connection = [CODE] if i put new here it says conn is not defined[/CODE] conn
        myCommand(1).CommandText = sqlquery
        myCommand(2).CommandText = sqlquery2
        myCommand(3).CommandText = sqlquery3
        myCommand(4).CommandText = sqlquery4
        myCommand(5).CommandText = sqlquery5
        myCommand(6).CommandText = sqlquery6
        myCommand(7).CommandText = sqlquery7
        'start query
        myAdapter(1).SelectCommand = myCommand(0)
        myAdapter(2).SelectCommand = myCommand(1)
        myAdapter(3).SelectCommand = myCommand(2)
        myAdapter(4).SelectCommand = myCommand(3)
        myAdapter(5).SelectCommand = myCommand(4)
        myAdapter(6).SelectCommand = myCommand(5)
        myAdapter(7).SelectCommand = myCommand(6)
        myAdapter(8).SelectCommand = myCommand(7)
        Dim mydata(6) As String
        mydata(0) = myCommand(1).ExecuteScalar
        mydata(1) = myCommand(2).ExecuteScalar
        mydata(2) = myCommand(3).ExecuteScalar
        mydata(3) = myCommand(4).ExecuteScalar
        mydata(4) = myCommand(5).ExecuteScalar
        mydata(5) = myCommand(6).ExecuteScalar
        mydata(6) = myCommand(7).ExecuteScalar
 
        profile.Label1.Text = mydata(0)
        profile.LinkLabel1.Text = mydata(1)
        profile.Label2.Text = mydata(3)
        profile.TextBox1.Text = mydata(4)
        profile.LinkLabel2.Text = mydata(5)
        profile.LinkLabel3.Text = mydata(6)
        profile.Label3.Text = mydata(7)
    End Sub

sooo i dont know what the heck is going on there and yes i know that my other command is wrong i just done know mysql so i dont know ware im going wrong yes i know that its written but but would it work better like this

sqlquery = "Insert Into userlist Where username='" & My.Settings.unam & "'" & "(name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"

sorry if i am ingoreing the ovius i just dont know this stuff very well yet

[techgnome]

incorrect:

sqlquery = "Insert Into userlist Where username='" & My.Settings.unam & "'" & "(name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"

correct:

sqlquery = "Insert Into userlist (name, site, message, about, youtube, myspace, email) VALUES( ?name, ?site, ?message, ?about, ?youtube, ?myspace, ?email)"

You didn't follow my egg carton example:

Dim carton(12) as egg = {new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg, new egg}

So... following that example...

Dim myCommand(8) As MySqlCommand = {new MySqlCommand, new MySqlCommand, new MySqlCommand, new MySqlCommand, new MySqlCommand, new MySqlCommand, new MySqlCommand, new MySqlCommand}

-tg