Problems with Insert Command

[Jnox]

I am having huge problems with my code.

I am trying to insert 1,2,3,4,5 for testing purposes into my SQL server table. It is not working and I am pulling my hair out trying to figure it out!!!!

HELPPPPPPPPPPPP!!!!!!!!!

Here is my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Dim connectionString As String = "uid=xxxxx;pwd=xxxxx;data source=LAPTOP\SQLEXPRESS;initial catalog=CheckWeigher"
Dim connection As New SqlConnection(connectionString)
connection.Open()
 
Dim adapter As SqlDataAdapter = New SqlDataAdapter
 
' Create the InsertCommand.
'Dim command = New SqlCommand("INSERT INTO Users (UserID, FirstName, LastName, Title, Password) " & "VALUES(1, 2, 3, 4, 5)", connection)
Dim command = New SqlCommand("INSERT INTO Users (UserID, FirstName, LastName, Title, Password) " & "VALUES(@UserID, @FirstName, @LastName, @Title, @Password)", connection)
 
 
' Add the parameters for the InsertCommand.
command.Parameters.Add("@UserID", SqlDbType.NVarChar, 255, TextBox2.Text)
command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, TextBox3.Text)
command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, TextBox4.Text)
command.Parameters.Add("@Title", SqlDbType.NVarChar, 50, TextBox5.Text)
command.Parameters.Add("@Password", SqlDbType.NVarChar, 50, TextBox10.Text)
 
adapter.InsertCommand = command
 
End Sub

 

[ashishnaicker]

wouldn't it be easier just to use

command.executeNonQuery()

rather than using a dataadapter, as I do not see any datasets there...

cheers

Ashish.

 

[Jnox]

Here is the updated code. Now it gives me an error saying :
An unhandled exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll

Additional information: Exception has been thrown by the target of an invocation.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Dim connectionString As String = "uid=xxxxx;pwd=xxxxx;data source=LAPTOP\SQLEXPRESS;initial catalog=CheckWeigher"
Dim connection As New SqlConnection(connectionString)
 
 
'Dim adapter As SqlDataAdapter = New SqlDataAdapter
 
' Create the InsertCommand.
Dim command = New SqlCommand("INSERT INTO Users (UserID, FirstName, LastName, Title, Password) VALUES (@UserID, @FirstName, @LastName, @Title, @Password)", connection)
 
 
' Add the parameters for the InsertCommand.
command.Parameters.Add("@UserID", SqlDbType.NVarChar, 255, TextBox2.Text)
command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, TextBox3.Text)
command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, TextBox4.Text)
command.Parameters.Add("@Title", SqlDbType.NVarChar, 50, TextBox5.Text)
command.Parameters.Add("@Password", SqlDbType.NVarChar, 50, TextBox10.Text)
 
connection.Open()
command.ExecuteNonQuery()
 
End Sub

 

[gmacek]

youhave to add SqlParameters to the command.Parameter collection like so:

command.Parameters.Add(New SqlParameter("@UserID", SqlDbType.NVarChar, 255, TextBox2.Text)

Hope this helps.
G.

 

[vis781]

command.Parameters.Add("@UserID", SqlDbType.NVarChar, 255, TextBox2.Text)
command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, TextBox3.Text)
command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, TextBox4.Text)
command.Parameters.Add("@Title", SqlDbType.NVarChar, 50, TextBox5.Text)
command.Parameters.Add("@Password", SqlDbType.NVarChar, 50, TextBox10.Text)

Here's your problem, the last argument for a parameter is the name of the original column in the database. So a parameter will look like this...

command.Parameters.Add("@UserID", SqlDbType.NVarChar, 255, [COLOR=blue]OrignalColumnName as string[/COLOR])
.Value = Textbox2.text

 

[gmacek]

Hi,

I looked at you code again and it is much to complicated. There is no need to add SqlParameters to your command when you use a direct SqlQuery. You do this only when using stored procedures.
So here is the code that does what you want:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'setup and populate the variables we want to write to the DB
Dim myUserID As String = "1234"
Dim myFirstName As String = "Joe"
Dim myLastName As String = "Doe"
Dim myTitle As String = "Boss"
Dim myPassword As String = "DontTell"
'Set up your SQL Query. Using a string.Format seems to be the simplest
' way to add the parameters to the query
Dim Query As String = String.Format("INSERT INTO Users (UserID, FirstName, LastName, Title, Password) VALUES({0},{1},{2},{3},{4})", myUserID, myFirstName, myLastName, myTitle, myPassword)
'set the connectionstring here
Dim connectionString As String = "uid=xxxxx;pwd=xxxxx;data source=LAPTOP\SQLEXPRESS;initial catalog=CheckWeigher"
Dim connection As New SqlConnection(connectionString)
'set the command here
Dim command As New SqlCommand(Query, connection)
'make sure the command type is direct query and not stored procedure
command.CommandType = CommandType.Text
'let's use as try/catch block to get the error message, just in case
Try
'open the connection to the DB
connection.Open()
'execute the command
command.ExecuteScalar()
Catch ex As Exception
'in case of an error bring up a message box
MessageBox.Show(ex.Message)
Finally
'make sure we close the connection and dispose of the command object
connection.Close()
command.Dispose()
End Try
End Sub

So I hope this solves your problem.

Have fun
G.

 

[Jnox]

Thank you for your replies guys, I will give it a try!

Jnox

 

[vis781]

Originally Posted By Gmacek
I looked at you code again and it is much to complicated. There is no need to add SqlParameters to your command when you use a direct SqlQuery.You do this only when using stored procedures

For someone trying to learn how INSERT, UPDATE etc i don't think this is good practice. Jnox has stated that this is for testing purposes, so better to learn how to use parameters early on rather than sometime later realising that he has not been going in the proper direction. Only use parameters for stored procedures? Really? I use parameters all the time, and i mostly deal with MS Access and OLEDB. How would you specify the correct type if you didn't use a parameter? Your example assumes that all the fields in the Db accept a string value, what if they didn't?

 

[Jnox]

For someone trying to learn how INSERT, UPDATE etc i don't think this is good practice. Jnox has stated that this is for testing purposes, so better to learn how to use parameters early on rather than sometime later realising that he has not been going in the proper direction. Only use parameters for stored procedures? Really? I use parameters all the time, and i mostly deal with MS Access and OLEDB. How would you specify the correct type if you didn't use a parameter? Your example assumes that all the fields in the Db accept a string value, what if they didn't?

You are correct. Now I am trying to figure out how to use the UPDATE and DELETE commands. I'm having tons of issues and I do need some help!!!!!!

Jnox

 

[cjard]

Here's to clear up some things:

gmacek's claim of "I looked at you code again and it is much to complicated. There is no need to add SqlParameters to your command when you use a direct SqlQuery. You do this only when using stored procedures." is not correct. There is a strong argument for using parameters at all times. If you do not, you render your database open to SQL injection attacks which can be used to gain unauthorised access or destroy data. The idea of parameterising a statement is that you have the program compile the statement once, then you simple modify the values before you run the statement again.

To this end, writing the whole thing in one procedure is moot; you would perhaps create the SQL Command object (that stores the sql text and the parameters) in a globally accessible place (or the top of the form if it is never used elsewhere), and just do it once

Additionally gmacek's code uses ExecuteScalar to run a DML statement. ExecuteScalar is used for running selection queries that return a single value. If your query returns one value then you should use ExecuteScalar and capture the return value:

"SELECT Count(*) FROM users"
...
Dim result as String = ...ExecuteScalar()
'result now contains the user count

ExecuteNonQuery() is used for running DML (data manipulation language) statements like CREATE TABLE, DROP VIEW, INSERT, UPDATE, DELETE..
Basically, if its a command that changes data but returns no rows, use ExecuteNonQuery()

If it returns 1 row of 1 column, Use ExecuteScalar

If it returns rows of columns (SELECT * FROM Users), use Execute()

 

[Schenz]

Vis781 and cjard, Thank you for making my points for me on why it is almost ALWAYS REQUIRED to use parameters.