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.InsertCommand = New SqlCommand("INSERT INTO billing( type,
dsc ) VALUES ( @type, @dsc)", MySqlConnection)

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

Could someone tell me what I'm doing wrong?

 

[Armin Zingler]

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.InsertCommand = New SqlCommand("INSERT INTO billing(
type, dsc ) VALUES ( @type, @dsc)", MySqlConnection)

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

Could someone tell me what I'm doing wrong?

Have a look @ microsoft.public.dotnet.framework.adonet

(how do you set the parameters' value?)


Armin

 

[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

 

[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'

 

[cj]

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

 

[Armin Zingler]

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

You should have a look at microsoft.public.dotnet.framework.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.Odbc
Imports System.Data.SqlClient

Public Class Form1
Inherits System.Windows.Forms.Form

Dim MyOdbcConnection As New OdbcConnection("Driver={INFORMIX......")
Dim MyOdbcAdapter As New OdbcDataAdapter

Dim MySqlConnection As New SqlConnection("This is a SQL Server")
Dim MySqlAdapter As New SqlDataAdapter
Dim MySqlCmdBuilder As New SqlCommandBuilder(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.
InitializeComponent()

'Add any initialization after the InitializeComponent() 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.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'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.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
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.SuspendLayout()
'
'startLbl
'
Me.startLbl.Location = New System.Drawing.Point(8, 72)
Me.startLbl.Name = "startLbl"
Me.startLbl.TabIndex = 0
'
'stopLbl
'
Me.stopLbl.Location = New System.Drawing.Point(120, 72)
Me.stopLbl.Name = "stopLbl"
Me.stopLbl.TabIndex = 1
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(8, 48)
Me.Label1.Name = "Label1"
Me.Label1.TabIndex = 2
Me.Label1.Text = "Start"
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(120, 48)
Me.Label2.Name = "Label2"
Me.Label2.TabIndex = 3
Me.Label2.Text = "Stop"
'
'Label3
'
Me.Label3.Location = New System.Drawing.Point(232, 48)
Me.Label3.Name = "Label3"
Me.Label3.TabIndex = 4
Me.Label3.Text = "Diff"
'
'diffLbl
'
Me.diffLbl.Location = New System.Drawing.Point(232, 72)
Me.diffLbl.Name = "diffLbl"
Me.diffLbl.Size = New System.Drawing.Size(216, 24)
Me.diffLbl.TabIndex = 5
'
'Label4
'
Me.Label4.Location = New System.Drawing.Point(8, 112)
Me.Label4.Name = "Label4"
Me.Label4.TabIndex = 6
Me.Label4.Text = "Records Read"
'
'Label5
'
Me.Label5.Location = New System.Drawing.Point(8, 136)
Me.Label5.Name = "Label5"
Me.Label5.TabIndex = 7
Me.Label5.Text = "Records Written"
'
'ReadLbl
'
Me.ReadLbl.Location = New System.Drawing.Point(120, 112)
Me.ReadLbl.Name = "ReadLbl"
Me.ReadLbl.TabIndex = 9
'
'WrittenLbl
'
Me.WrittenLbl.Location = New System.Drawing.Point(120, 136)
Me.WrittenLbl.Name = "WrittenLbl"
Me.WrittenLbl.TabIndex = 10
'
'TableBox
'
Me.TableBox.Location = New System.Drawing.Point(8, 8)
Me.TableBox.Name = "TableBox"
Me.TableBox.Size = New System.Drawing.Size(216, 20)
Me.TableBox.TabIndex = 11
Me.TableBox.Text = ""
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(232, 8)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 12
Me.Button1.Text = "Go"
'
'Form1
'
Me.AutoScaleBaseSize = 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Button1.Enabled = False

currentTable = TableBox.Text.Trim

MyOdbcAdapter.SelectCommand = New OdbcCommand("select * from "
& currentTable, MyOdbcConnection)
MyOdbcAdapter.AcceptChangesDuringFill = False
startTime = Now
startLbl.Text = Format(startTime, "hh:mm:ss:ffff tt")

Try
MyOdbcAdapter.Fill(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.SelectCommand = New SqlCommand("select * from
" & currentTable, MySqlConnection)
MySqlAdapter.InsertCommand = 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.GetInsertCommand.CommandText.ToString.IndexOf(" 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.GetInsertCommand.CommandText.ToString.Replace(" 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.GetInsertCommand.CommandText.ToString.Replace(" desc ",
" [desc] ")
A = MyFixedCmd.IndexOf("(")
B = MyFixedCmd.IndexOf(")")
IntoStr = MyFixedCmd.Substring(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.InsertCommand = New SqlCommand(MyFixedCmd,
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.Update(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:ffff tt")
diffLbl.Text = "Transferring " & MyDt.Rows.Count() & " rows
took " & DateDiff(DateInterval.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.Critical, "Error")
MsgBox("There are " & MyDt.Rows.Count() & " rows in
MyDt.", MsgBoxStyle.Critical, "Error")
End If

MySqlAdapter.Fill(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.Critical, "Error")
MsgBox("There are " & MyDt.Rows.Count() & " rows in
MyDt.", MsgBoxStyle.Critical, "Error")
End If
End If

Button1.Enabled = True
End Sub
End Class

 

[Armin Zingler]

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 OracleConnection are all part of ADO.net:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconadonetconnections.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 SqlCommandBuilder. 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.

'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO Employees(LastName,
FirstName) VALUES (@LastName, @FirstName); SELECT" & _
" EmployeeID, LastName, FirstName FROM Employees WHERE (EmployeeID =
@@IDENTITY)"
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.NVarChar, 20, "LastName"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.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.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Employees", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"), New
System.Data.Common.DataColumnMapping("LastName", "LastName"), New
System.Data.Common.DataColumnMapping("FirstName", "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, 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 OracleConnection are all part of ADO.net:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconadonetconnections.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 SqlCommandBuilder. 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

 

[cj]

Cor, I think I understand the parameters your talking about now. Sounds
like a lot of work. I've never done anything like that. I've either
had the wizard set up the commands for a specific table or I've
generated my own command with values being variables from my program and
executed the insert or update statement outside of the dataadapter.

This was a unique program for me and it seems not common for other folks
as well. I couldn't use the design time wizard and I couldn't or at
least it would be quite hard to generate specific insert commands at
runtime. So I wanted VB to do this for me but couldn't get it to work.
Armin provided the solution to my problem.