Question Insert into table from form , into sql server 2008

[JohnDW]

Dear forum members,

I am using vb 2010 at placing data in table.
I used following code

Private Sub Button3_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click


Dim MyConnection As SqlConnection = Nothing
As SqlTransaction = Nothing Dim MyTransaction



MyConnection = New SqlConnection (Constant)
MyConnection.Open ()
MyTransaction = MyConnection.BeginTransaction


Dim SQL As String = "insert into Orders (Customer ID) values ​​(: 0)"
As New SqlCommand Dim CMD1
CMD1.Connection MyConnection =
CMD1.Transaction = MyTransaction
CMD1.CommandText = SQL
CMD1.Parameters.AddWithValue (": 0," txtUsernumber.Text)

MessageBox.Show (SQL)

The message box, however, shows a value of 0. (Insert into Orders (Customer ID) values ​​ 0))

l wan't the value ( the Usernumber
In the form appears in txtUsernumber each customer number.

Can anyone help out?

Grt,

John

 

[jmcilhinney]

Firstly, I have wrapped your code in formatting tags for readability. Please do so for us in future.

You've got at least four, maybe five separate issues going on there.

You should generally avoid spaces and other special characters in table and column names. If you possibly can, change your "Customer ID" column to "CustomerID". If you have no choice but to use spaces then you must wrap such identifiers in brackets in SQL code.

SQL Server doesn't use a colon as a parameter prefix. That is an Oracle thing. SQL Server uses @. Also, you can't leave spaces in your parameter names. You really should be naming the parameter after the column it is intended for too.

When you call AddWithValue your are passing a String as the value. If the column has a numeric data type then what you should be doing is converting the String to the appropriate numeric type first and then passing the result. The data type is inferred from the value when calling AddWithValue so the value must be the correct type.

Dim SQL As String = "INSERT INTO Orders (CustomerID) VALUES ​​(@CustomerID)"

As New SqlCommand Dim CMD1

CMD1.Connection = MyConnection

CMD1.Transaction = MyTransaction

CMD1.CommandText = SQL

CMD1.Parameters.AddWithValue ("@CustomerID", CInt(txtUsernumber.Text))

Even if you do all that though, you're still not going to see what you expect, but that's not a problem. All you're doing is displaying the String you created. The String you created hasn't changed because you added a parameter to the command. The SQL code and the parameters are passed to the database separately and the substitution is made there, not in your app.

 

[jmcilhinney]

By the way, is there really a need to use a transaction? Are you executing multiple commands?

 

[JohnDW]

By the way, is there really a need to use a transaction? Are you executing multiple commands?

Thanks for your reply.
I'm trying it out and let the result know.
I'm building an application and the form with the code (I'm working on it) must indeed execute multiple commands.
Thanks,
John

 

[JohnDW]

Transaction Problem

First: How do I wrap my code in formatting tags? (I putted numbers manually)
The Code:
[BEGIN CODE]
1. Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim Cnst As String = "Data Source=BF2\maurann;Initial Catalog=maurann;Integrated Security=SSPI;"
2. Dim MyConnection As SqlConnection = Nothing
3. Dim MyTransaction As SqlTransaction = Nothing
4. MyConnection = New SqlConnection(Cnst)
5. MyConnection.Open()
6. MyTransaction = MyConnection.BeginTransaction
7. Dim SQL As String = "insert into Orderscope (Klantnummer) values (@Klantnummer)"
8. Using connection As New SqlConnection(Cnst)
9. Dim CMD1 As New SqlCommand(SQL, MyConnection)
10.CMD1.Parameters.AddWithValue("@Klantnummer", CInt(txtKlantnummer.Text))
11.CMD1.ExecuteNonQuery()
12.CMD1.Dispose()
13.End Using
14.MyConnection.Close()
[END CODE]
The error I get is
Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction.
The Transaction property of the command has not been initialized.

What I am doing wrong?

Grtz,
John

 

[jmcilhinney]

Look at your first post and how I edited it to answer your question about formatting tags. As for the error, if you call BeginTransaction on a connection object, every command that uses that connection must have that transaction assigned to its Transaction property until you either commit or roll back the transaction. In short, if there is a transaction in effect then all commands must use it. Otherwise, why create the transaction in the first place?

 

[JohnDW]

Txs, I've deleted the call BeginTrasaction, and it works fine now.

What I want to do is to give the table in sql server a primary key 'OrdersID'.
If I insert the column 'klantnummer' now i get the message:

Cannot insert the value NULL into column 'OrdersId', table 'maurann.dbrderscope'; column does not allow nulls. INSERT fails.
The statement has been terminated.

How do I manage this?
Doesn't sql server acts like access, where there's a autonummering of the
primary key.

Grts,

John

 

[jmcilhinney]

If a column is not nullable then you need to provide a value. Either you need to provide that value or it needs to be generated by the database. If it's SQL Server then that means making it an identity.

 

[JohnDW]

If a column is not nullable then you need to provide a value. Either you need to provide that value or it needs to be generated by the database. If it's SQL Server then that means making it an identity.

Txs,

It works.

John

 

[JohnDW]

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]Dim radioButtons = [/COLOR][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]RadioButton[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]() {Rb1, Rb2, Rb3}[/COLOR]
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] radioButtonValue = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]Array[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].IndexOf(radioButtons, radioButtons.Single([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](rb) rb.Checked))

[/SIZE][/FONT][/SIZE][/FONT]

One problem I have with this array,
it gives the sql server only 2 values​​, 0 and 1.
The third index (= number 2) is not displayed. Instead,
the code also inserts 1.
Any idea why this is happening?

Grt,

John

 

[jmcilhinney]

There's no valid reason for that to happen if you've done everything correctly. This is what debugging is for. You can run the code and see exactly what happens at every step and, therefore, where the incorrect value comes from. Maybe it's not even from that code but from where you're saving it. We can only guess because all we know is that you have two lines of code there that will do what they're supposed to.

 

[JohnDW]

Allright,
the field in sql server had Byte as value, I've changed it into smallint and it works. Great!