writing data adapter sql commands

[cj]

I'm having a problem writing an insert command to work with a datatable.
I've looked at what the sqldataadapter creates and created my insert
command to look the same. I have:
MySqlAdapter.In sertCommand = New SqlCommand("INS ERT INTO billing( type,
dsc ) VALUES ( @type, @dsc)", MySqlConnection )

But it gives me the following error when MySqlAdapter.Up date(MyDt)
executes: Must declare the variable '@type'

Could someone tell me what I'm doing wrong?

[Cor Ligthert [MVP]]

CJ,

If you did what I wrote, than you had seen direct your current problem.

Just use the dataadapter wizard temporaly to create your SQL strings and cut
them of the designer part of your program to set them in the method where
you need them at runtime.

The Insert command needs the "Select" code to see if the data is not changed
already.

Please keep it to the original thread.

Cor

[Armin Zingler]

"cj" <[e-mail] am> schrieb

I'm having a problem writing an insert command to work with a
datatable. I've looked at what the sqldataadapter creates and
created my insert command to look the same. I have:
MySqlAdapter.In sertCommand = New SqlCommand("INS ERT INTO billing(
type, dsc ) VALUES ( @type, @dsc)", MySqlConnection )

But it gives me the following error when MySqlAdapter.Up date(MyDt)
executes: Must declare the variable '@type'

Could someone tell me what I'm doing wrong?

Have a look @ microsoft.publi c.dotnet.framew ork.adonet

(how do you set the parameters' value?)
Armin

[cj]

Sorry, that doesn't work. I used the wizard which created this line.

INSERT INTO billing(type, dsc) VALUES(@type, @dsc); SELECT type, dsc
FROM billing

I then used that command in code and it doesn't work. It gives me the
same error.

Must declare the variable '@type'
Cor Ligthert [MVP] wrote:

CJ,

If you did what I wrote, than you had seen direct your current problem.

Just use the dataadapter wizard temporaly to create your SQL strings and cut
them of the designer part of your program to set them in the method where
you need them at runtime.

The Insert command needs the "Select" code to see if the data is not changed
already.

Please keep it to the original thread.

Cor

[cj]

I'm sorry, I don't understand what you are saying.
Armin Zingler wrote:

"cj" <[e-mail] am> schrieb

I'm having a problem writing an insert command to work with a
datatable. I've looked at what the sqldataadapter creates and
created my insert command to look the same. I have:
MySqlAdapter.In sertCommand = New SqlCommand("INS ERT INTO billing(
type, dsc ) VALUES ( @type, @dsc)", MySqlConnection )

But it gives me the following error when MySqlAdapter.Up date(MyDt)
executes: Must declare the variable '@type'

Could someone tell me what I'm doing wrong?

Have a look @ microsoft.publi c.dotnet.framew ork.adonet

(how do you set the parameters' value?)
Armin

[Armin Zingler]

"cj" <[e-mail] am> schrieb

I'm sorry, I don't understand what you are saying.

You should have a look at microsoft.publi c.dotnet.framew ork.adonet for
ADO.Net related questions. There you should show how you set the parameters'
value.
Armin

[cj]

Armin, you and Cor sound like you know what your doing. I'm pretty new
to .net. I really don't understand why I need to set parameters and I'm
not using ADO.Net.

Perhaps this would help. Below is my entire program. Only the
connection strings have been removed for security. It isn't long. I'm
sure an expert like one of you would have no problem showing me how to
fix it. I have commented the code as best I can to show the problem area.

Please do not simply say the code is junk and suggest I try another way
completely. To help me learn and understand what is happening I need to
see what is wrong with what I am doing. Eventually I might find a
better way to write all this.

Thanks for your help. Below is the code you can paste it right into a
blank windows application.
Imports System.Data.Odb c
Imports System.Data.Sql Client

Public Class Form1
Inherits System.Windows. Forms.Form

Dim MyOdbcConnectio n As New OdbcConnection( "Driver={INFORM IX......")
Dim MyOdbcAdapter As New OdbcDataAdapter

Dim MySqlConnection As New SqlConnection(" This is a SQL Server")
Dim MySqlAdapter As New SqlDataAdapter
Dim MySqlCmdBuilder As New SqlCommandBuild er(MySqlAdapter )

Dim MyDt As New DataTable
Dim currentTable As String
Dim startTime As Date
Dim stopTime As Date

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeCompo nent()

'Add any initialization after the InitializeCompo nent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Disp ose()
End If
End If
MyBase.Dispose( disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.Componen tModel.IContain er

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents startLbl As System.Windows. Forms.Label
Friend WithEvents Label1 As System.Windows. Forms.Label
Friend WithEvents Label2 As System.Windows. Forms.Label
Friend WithEvents Label3 As System.Windows. Forms.Label
Friend WithEvents diffLbl As System.Windows. Forms.Label
Friend WithEvents stopLbl As System.Windows. Forms.Label
Friend WithEvents Label4 As System.Windows. Forms.Label
Friend WithEvents Label5 As System.Windows. Forms.Label
Friend WithEvents ReadLbl As System.Windows. Forms.Label
Friend WithEvents WrittenLbl As System.Windows. Forms.Label
Friend WithEvents Button1 As System.Windows. Forms.Button
Friend WithEvents TableBox As System.Windows. Forms.TextBox
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub
InitializeCompo nent()
Me.startLbl = New System.Windows. Forms.Label
Me.stopLbl = New System.Windows. Forms.Label
Me.Label1 = New System.Windows. Forms.Label
Me.Label2 = New System.Windows. Forms.Label
Me.Label3 = New System.Windows. Forms.Label
Me.diffLbl = New System.Windows. Forms.Label
Me.Label4 = New System.Windows. Forms.Label
Me.Label5 = New System.Windows. Forms.Label
Me.ReadLbl = New System.Windows. Forms.Label
Me.WrittenLbl = New System.Windows. Forms.Label
Me.TableBox = New System.Windows. Forms.TextBox
Me.Button1 = New System.Windows. Forms.Button
Me.SuspendLayou t()
'
'startLbl
'
Me.startLbl.Loc ation = New System.Drawing. Point(8, 72)
Me.startLbl.Nam e = "startLbl"
Me.startLbl.Tab Index = 0
'
'stopLbl
'
Me.stopLbl.Loca tion = New System.Drawing. Point(120, 72)
Me.stopLbl.Name = "stopLbl"
Me.stopLbl.TabI ndex = 1
'
'Label1
'
Me.Label1.Locat ion = New System.Drawing. Point(8, 48)
Me.Label1.Name = "Label1"
Me.Label1.TabIn dex = 2
Me.Label1.Text = "Start"
'
'Label2
'
Me.Label2.Locat ion = New System.Drawing. Point(120, 48)
Me.Label2.Name = "Label2"
Me.Label2.TabIn dex = 3
Me.Label2.Text = "Stop"
'
'Label3
'
Me.Label3.Locat ion = New System.Drawing. Point(232, 48)
Me.Label3.Name = "Label3"
Me.Label3.TabIn dex = 4
Me.Label3.Text = "Diff"
'
'diffLbl
'
Me.diffLbl.Loca tion = New System.Drawing. Point(232, 72)
Me.diffLbl.Name = "diffLbl"
Me.diffLbl.Size = New System.Drawing. Size(216, 24)
Me.diffLbl.TabI ndex = 5
'
'Label4
'
Me.Label4.Locat ion = New System.Drawing. Point(8, 112)
Me.Label4.Name = "Label4"
Me.Label4.TabIn dex = 6
Me.Label4.Text = "Records Read"
'
'Label5
'
Me.Label5.Locat ion = New System.Drawing. Point(8, 136)
Me.Label5.Name = "Label5"
Me.Label5.TabIn dex = 7
Me.Label5.Text = "Records Written"
'
'ReadLbl
'
Me.ReadLbl.Loca tion = New System.Drawing. Point(120, 112)
Me.ReadLbl.Name = "ReadLbl"
Me.ReadLbl.TabI ndex = 9
'
'WrittenLbl
'
Me.WrittenLbl.L ocation = New System.Drawing. Point(120, 136)
Me.WrittenLbl.N ame = "WrittenLbl "
Me.WrittenLbl.T abIndex = 10
'
'TableBox
'
Me.TableBox.Loc ation = New System.Drawing. Point(8, 8)
Me.TableBox.Nam e = "TableBox"
Me.TableBox.Siz e = New System.Drawing. Size(216, 20)
Me.TableBox.Tab Index = 11
Me.TableBox.Tex t = ""
'
'Button1
'
Me.Button1.Loca tion = New System.Drawing. Point(232, 8)
Me.Button1.Name = "Button1"
Me.Button1.TabI ndex = 12
Me.Button1.Text = "Go"
'
'Form1
'
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.ClientSize = New System.Drawing. Size(456, 213)
Me.Controls.Add (Me.Button1)
Me.Controls.Add (Me.TableBox)
Me.Controls.Add (Me.WrittenLbl)
Me.Controls.Add (Me.ReadLbl)
Me.Controls.Add (Me.Label5)
Me.Controls.Add (Me.Label4)
Me.Controls.Add (Me.diffLbl)
Me.Controls.Add (Me.Label3)
Me.Controls.Add (Me.Label2)
Me.Controls.Add (Me.Label1)
Me.Controls.Add (Me.stopLbl)
Me.Controls.Add (Me.startLbl)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout (False)

End Sub

#End Region

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Button1.Enabled = False

currentTable = TableBox.Text.T rim

MyOdbcAdapter.S electCommand = New OdbcCommand("se lect * from "
& currentTable, MyOdbcConnectio n)
MyOdbcAdapter.A cceptChangesDur ingFill = False
startTime = Now
startLbl.Text = Format(startTim e, "hh:mm:ss:f fff tt")

Try
MyOdbcAdapter.F ill(MyDt)
Catch ex As Exception
MessageBox.Show ("Fill error: " & ex.Message)
Button1.Enabled = True
Exit Sub
End Try
ReadLbl.Text = MyDt.Rows.Count ()

If MyDt.Rows.Count () > 0 Then
MySqlAdapter.Se lectCommand = New SqlCommand("sel ect * from
" & currentTable, MySqlConnection )
MySqlAdapter.In sertCommand = Nothing
MySqlCmdBuilder .RefreshSchema( )

'--Some tables this program will copy will have fields
named desc with SQL Server doesn't like.
'--SQL Server needs desc changed to [desc] in order to work

'--This IF is remarked out so the code will execute even if
desc field is not present so I can test
'--the generation and assignment of an insert command w/o
worrying that the problems are unique to
'--tables containing a desc field.

'If
MySqlCmdBuilder .GetInsertComma nd.CommandText. ToString.IndexO f(" desc ")
<> -1 Then
Dim MyFixedCmd, IntoStr, ValStr As String
Dim A, B As Integer

'--The below line of remarked out code doesn't work
'MyFixedCmd =
MySqlCmdBuilder .GetInsertComma nd.CommandText. ToString.Replac e(" desc ",
" [desc] ")

'--Looking at what the datawizard generates for
insertcommands I figured I needed to modify the
'--command using the below code. Again it doesn't work.
MyFixedCmd =
MySqlCmdBuilder .GetInsertComma nd.CommandText. ToString.Replac e(" desc ",
" [desc] ")
A = MyFixedCmd.Inde xOf("(")
B = MyFixedCmd.Inde xOf(")")
IntoStr = MyFixedCmd.Subs tring(A + 1, (B) - (A + 1)).Trim
IntoStr = IntoStr.Replace (" ,", ",")
ValStr = IntoStr.Replace (", ", ", @").Trim
MyFixedCmd = "INSERT INTO " & currentTable & "(" & IntoStr
& ") VALUES(@" & ValStr & "); SELECT " & IntoStr & " FROM " & currentTable
MySqlAdapter.In sertCommand = New SqlCommand(MyFi xedCmd,
MySqlConnection )
'End If

'--The rest of this program code works great if I let
MySqlCmdBuilder provide the insert code
'--and do not set the InsertCommand in MySqlAdapter using
the above IF statement. If the above IF
'--statement is used the below update method will crash
with: Must declare the variable and whatever
'--the first field/variable is.
Try
MySqlAdapter.Up date(MyDt)
Catch ex As Exception
MessageBox.Show ("Update error: " & ex.Message)
Button1.Enabled = True
Exit Sub
End Try

stopTime = Now
stopLbl.Text = Format(stopTime , "hh:mm:ss:f fff tt")
diffLbl.Text = "Transferri ng " & MyDt.Rows.Count () & " rows
took " & DateDiff(DateIn terval.Second, startTime, stopTime) & " seconds"

'Verification follows

MyDt.Clear()
If MyDt.Rows.Count () <> 0 Then
MsgBox("There shouldn't be any rows in MyDt now. #1",
MsgBoxStyle.Cri tical, "Error")
MsgBox("There are " & MyDt.Rows.Count () & " rows in
MyDt.", MsgBoxStyle.Cri tical, "Error")
End If

MySqlAdapter.Fi ll(MyDt)
WrittenLbl.Text = MyDt.Rows.Count ()

MyDt.Clear()
If MyDt.Rows.Count () <> 0 Then
MsgBox("There shouldn't be any rows in MyDt now. #2",
MsgBoxStyle.Cri tical, "Error")
MsgBox("There are " & MyDt.Rows.Count () & " rows in
MyDt.", MsgBoxStyle.Cri tical, "Error")
End If
End If

Button1.Enabled = True
End Sub
End Class

Armin Zingler wrote:

"cj" <[e-mail] am> schrieb

I'm sorry, I don't understand what you are saying.

You should have a look at microsoft.publi c.dotnet.framew ork.adonet for
ADO.Net related questions. There you should show how you set the
parameters' value.
Armin

[Armin Zingler]

"cj" <[e-mail] am> schrieb

Armin, you and Cor sound like you know what your doing. I'm pretty
new to .net. I really don't understand why I need to set parameters
and I'm not using ADO.Net.

No, you do use ADO.Net. The SqlConnection, OdbcConnection, Oledbconnection
and OracleConnectio n are all part of ADO.net:

http://msdn.microsoft.com/library/en...onnections.asp

What I was trying to say is that I thought your problem is not related to
VB.Net. It is a more common problem, /not related/ to a specific
programming language even though you are /using/ a specific language.

I think I know VB.Net quite well and I hope I can help with many VB.Net
language related problems, but ADO.Net is nothing that is related to the
language. It is a part of the Framework that can be discussed even without
any language (apart from English ;-) ). If I wanted to help people having
ADO.Net problems, I would read the ADO.Net group. Now I am reading here and
still find ADO.net specific discussions. That's why I tried to kindly point
you to the ADO.Net group.

Nevermind, we can keep on leading the started discussion /here/, of course.
:)
Perhaps this would help. Below is my entire program. Only the connection
strings have been removed for security. It isn't long. I'm sure an
expert like one of you would have no problem showing me how to fix it. I
have commented the code as best I can to show the problem area.

Please do not simply say the code is junk and suggest I try another way
completely. To help me learn and understand what is happening I need to
see what is wrong with what I am doing. Eventually I might find a better
way to write all this.

Thanks for your help. Below is the code you can paste it right into a
blank windows application.

First, you should enable Options Strict. Turning off compiler checks can
lead to unnecessary faults at runtime.

I didn't analyze the code completely, but maybe all you are looking for are
the QuotePrefix and QuoteSuffix properties of the SqlCommandBuild er. If you
set

MySqlCmdBuilder .QuotePrefix = "["
MySqlCmdBuilder .QuoteSuffix = "]"

then you probably don't have to parse the Insertcommand. I can't test it
because I don't have an SqlServer here.
Armin

[Cor Ligthert [MVP]]

CJ,

If I make in the designer using Northwind I get this.

'SqlInsertComma nd1
'
Me.SqlInsertCom mand1.CommandTe xt = "INSERT INTO Employees(LastN ame,
FirstName) VALUES (@LastName, @FirstName); SELECT" & _
" EmployeeID, LastName, FirstName FROM Employees WHERE (EmployeeID =
@@IDENTITY)"
Me.SqlInsertCom mand1.Connectio n = Me.SqlConnectio n1
Me.SqlInsertCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@LastName ",
System.Data.Sql DbType.NVarChar , 20, "LastName") )
Me.SqlInsertCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@FirstNam e",
System.Data.Sql DbType.NVarChar , 10, "FirstName" ))
'

Setting the parameters is of course a part of this.

Depending where you use it, you maybe have to add in advance.
Me.SqlDataAdapt er1.TableMappin gs.AddRange(New
System.Data.Com mon.DataTableMa pping() {New
System.Data.Com mon.DataTableMa pping("Table", "Employees" , New
System.Data.Com mon.DataColumnM apping() {New
System.Data.Com mon.DataColumnM apping("Employe eID", "EmployeeID "), New
System.Data.Com mon.DataColumnM apping("LastNam e", "LastName") , New
System.Data.Com mon.DataColumnM apping("FirstNa me", "FirstName")})} )

I hope this helps.

Cor

[cj]

Armin,

I added MySqlCmdBuilder .QuotePrefix = "[" and
MySqlCmdBuilder .QuoteSuffix = "]" to my form load event and like you
said it works with out needing to parse the insert statement. Thanks!

Eventually I'll figure out all the tricks in .net. I hope this program
is done now but every time I think it is I run into another problem.

Thanks again for the excellent help!
cj
Armin Zingler wrote:

"cj" <[e-mail] am> schrieb

Armin, you and Cor sound like you know what your doing. I'm pretty
new to .net. I really don't understand why I need to set parameters
and I'm not using ADO.Net.

No, you do use ADO.Net. The SqlConnection, OdbcConnection, Oledbconnection
and OracleConnectio n are all part of ADO.net:

http://msdn.microsoft.com/library/en...onnections.asp
What I was trying to say is that I thought your problem is not related to
VB.Net. It is a more common problem, /not related/ to a specific
programming language even though you are /using/ a specific language.

I think I know VB.Net quite well and I hope I can help with many VB.Net
language related problems, but ADO.Net is nothing that is related to the
language. It is a part of the Framework that can be discussed even without
any language (apart from English ;-) ). If I wanted to help people having
ADO.Net problems, I would read the ADO.Net group. Now I am reading here and
still find ADO.net specific discussions. That's why I tried to kindly point
you to the ADO.Net group.

Nevermind, we can keep on leading the started discussion /here/, of course.
:)

Perhaps this would help. Below is my entire program. Only the
connection
strings have been removed for security. It isn't long. I'm sure an
expert like one of you would have no problem showing me how to fix it. I
have commented the code as best I can to show the problem area.

Please do not simply say the code is junk and suggest I try another way
completely. To help me learn and understand what is happening I need to
see what is wrong with what I am doing. Eventually I might find a better
way to write all this.

Thanks for your help. Below is the code you can paste it right into a
blank windows application.

First, you should enable Options Strict. Turning off compiler checks can
lead to unnecessary faults at runtime.

I didn't analyze the code completely, but maybe all you are looking for are
the QuotePrefix and QuoteSuffix properties of the SqlCommandBuild er. If you
set

MySqlCmdBuilder .QuotePrefix = "["
MySqlCmdBuilder .QuoteSuffix = "]"

then you probably don't have to parse the Insertcommand. I can't test it
because I don't have an SqlServer here.
Armin