Invalid object name sql server query

[securonic]

Hi everyone,

i have put together some code to execute a search on a specific columb in a sql server database. very simple really and it will be used as part of a larger system eventually

little problem though.....

Public Class Form1

Private Sub sb1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles sb1.Click

Dim con As New SqlClient.SqlConnection()
con.ConnectionString = "Data Source=213.***.***.1;Initial Catalog=clients;Persist Security Info=True;User ID=tom;Password=jones"
Dim cmd As New SqlCommand
Dim sqlstr As String

sqlstr = "SELECT * FROM clients WHERE name=" & "'" & textbox.Text & "'"
cmd.CommandText = sqlstr
cmd.Connection = con

Try
con.Open()
cmd.ExecuteNonQuery()

Finally
con.Close()
End Try
End Sub

Private Sub con(ByVal p1 As Object)
Throw New NotImplementedException
End Sub
End Class

only problem is i seem to be getting a problem when the line that executes the sql command string returns an exception 'invalid object name 'clients'. can anyone lend a hand in what this means?? been searching the net for a while now and still scratching my bonce.

many thanks

 

[jmcilhinney]

You don't have a table named 'clients' in your database.

Also, why do this:

sqlstr = "SELECT * FROM clients WHERE name=" & "'" & textbox.Text & "'"

when this does exactly the same thing:

sqlstr = "SELECT * FROM clients WHERE name='" & textbox.Text & "'"

What point is there to concatenating two literal strings?

Also, you shouldn't use string concatenation at all to insert variables into SQL code. There are various reasons that I won't go into here. Follow the Blog link in my signature and check out my post on ADO.NET Parameters for more information.

 

[kulrom]

Also if you plan to fetch some data or see if certain name exists your code will not work AS IS.
Unless you have output parameters and use Stored procedure cmd.ExecuteNonQuery() will not give you any data.
You can also use a Return parameter instead output. However this code will not work. Please explain what you expect of the code and i will post you appropriate example.

 

[securonic]

Hi and thanks for the feedback there,

I really am quite new to VB.net so you will have to excuse my probable obvious shortfalls here. jmcilhinney, I wasn't aware i would be able to use a variable within a sql command in such a way so assumed i would have to concatenate. upto now it is the only way i saw how so thanks for the tip! i will ammend that.

The code itself I was aware would not give me any sort of feedback as yet, I guess i was working on that part... Really i just wanted to know if the current code written would actually run without error so I could move onto the receiving data part of the code as i went. I am still getting to grips with databases so i was debugging as i went on.

I want you to know that i am putting this together purely for my own education in VB.net. if i can start to get the basics then i can carry on from there. What i was looking to actually achieve was a fairly simple search box and button which would carry out a second name search within a remote database and confirm with a message box whether it existed or not. I don't want to view or modify any tables ect, just a search box and confirmation action. I was assuming that i would not need to make a complete copy of the remote database back in code in order to do this and simply get a boolean output of yes or no dependant on outcome from the query direct to remote sql server.

just to note, i have an active remote sql server to test with as you can see in the existing code and a little vb prog i have been using to add/del/mod visible records. the later being fairly simple to setup in vb hence the reasoning behind the creation of this little project.

Thanks, look forward to your replys!

 

[kulrom]

Ok in that case you should use SqlDataReader object to see if that username exists e.g.

Try
con.Open()
' cmd.ExecuteNonQuery() - useless 
Dim reader As SqlDataReader = cmd.ExecuteReader()
If reader.Read() Then
    ' user exists 
Else
    ' user doesn't exist
End If
reader.Close()
Finally
con.Close()
End Try

 

[securonic]

Hi kulrom,
Thanks for the code example, very much appreciated! so it looks like i should lookup DataReader Obj for this job. The 'cmd.ExecutenNonquery() I take it is example of my useless existing code? just clarifying...

So i still use the same sql query string stored in cmd (edited according to suggestion by jmcilhinney of course) and use it in a method of SqlDataReader right?

I am then able to test the output with an 'if then' to see if reader.read returns a true or false for that particular record search and plonk some code in for user info. I just want to make sure i have the theory of that correct but seems pretty straight forward.

Great help, can't thank you enough. i would have been going for some time trying to find the right object to use for that. talk about barking up the wrong tree lol. i shall implement that code and see how i get on.

cheers!

 

[kulrom]

That's right! It works exactly as you described it ... the same select statement (edited according jmcilhinney's suggestion) and then you use an IF statement checking if reader reads or doesn't.

 

[securonic]

Hi Kulrom

i have decided to try and implement the code example you gave me with a mysql database. i have installed the mysql connector driver 6.3.4 which apears to have integrated with vs2010 and i have added the ref's to the project properties. if i run the following code i get the message box connected pop up as illustrated but the code breaks on the next line at 'reader = cmd.ExecuteReader()' with the msg 'InvalidOperationException was unhandled' 'Connection must be valid and open', when the code is ran during debug.

Try
connection.Open()
MsgBox("connected")
reader = cmd.ExecuteReader()
If reader.Read() Then
MsgBox("plate exists")
Else
MsgBox("plate does not exist")
End If
reader.Close()
Catch myerror As MySqlException
MsgBox(myerror)
Finally

End Try
connection.Close()
connection.Dispose()
End Using
End Using
End Sub

the command string is the following : Using cmd As New MySqlCommand("SELECT * FROM platetable WHERE plate='" & TextBox1.Text & "'") is the error referring to an invalid command string because i am using a different sql format?

i have used mysql admin to create a table with columbs so i am pretty sure i havn't missed anything out here..

any help would be greatly appreciated.

Dan

 

[kulrom]

You have to assign the connection to the command. e.g.

Dim connection As New MySqlConnection("connstring")
Try
   connection.Open()
   Dim command As MySqlCommand = connection.CreateCommand
   command.CommandText = "SELECT * FROM platetable WHERE plate='" & TextBox1.Text & "'"
   Dim reader As MySqlDataReader = command.ExecuteReader()
   If reader.Read() Then
       MessageBox.Show("plate exists")
   Else
       MessageBox.Show("plate does not exist")
   End If
   reader.Close()
Finally
   connection.Close()
End Try

 

[securonic]

Hi,

i figured it out in the end.... in my newbi stupidity i neglected to add the connection string to the mysqlcommand. works a treat now!

cheers all the same guys. your a great help

 

[securonic]

lol, realised you pipped me to the reply there. I must have been typing my discovery as you reply'd. cheers for the heads up!