Q: Using the SQLCommand gives me the creepers... Help!

  • Thread starter Thread starter Visual Systems AB \(Martin Arvidsson\)
  • Start date Start date
V

Visual Systems AB \(Martin Arvidsson\)

Hi!

I have been trying for several hours now to get this SQLCommand to work...

Below is a snippet... I have cut away some code...

Inherits System.Web.UI.Page
Protected WithEvents SqlConnection As System.Data.SqlClient.SqlConnection
Protected WithEvents tbKundnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents SqlCommand As System.Data.SqlClient.SqlCommand

Private Sub InitializeComponent()
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New
System.Configuration.AppSettingsReader()
Me.SqlConnection = New System.Data.SqlClient.SqlConnection()
Me.SqlCommand = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection
'
Me.SqlConnection.ConnectionString =
CType(configurationAppSettings.GetValue("SQL.Connection",
GetType(System.String)), String)
'
'SqlCommand
'
Me.SqlCommand.CommandText = "[InsertCustomer]"
Me.SqlCommand.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand.Connection = Me.SqlConnection
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(10, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KundNummer_1",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False,
CType(10, Byte), CType(0, Byte), "tbKundnummer",
System.Data.DataRowVersion.Current, Nothing))
End Sub

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection.Open()
End Sub

Private Sub bnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bnSave.Click
SqlCommand.ExecuteNonQuery() // Executing this will popup a message
InsertCustomer is missing a parameter @Kundnummer_1
End Sub

Why does this not work?

If i use
SqlCommand.Parameters.Add("@Kundnummer_1", tbKundnummer.text)
SqlCommand.ExecuteNonQuery

it will work...

Am i missing something?

Regards

Martin Arvidsson
 
It's not that hard to construct a Parameter object. Your final example
simply uses the defaults. The code generated by the DataAdapterConfiguraiton
wizard uses the all-encompassing constructor you're using--it's overkill for
most applications that don't need updatability.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
So basicly what you are telling me is that i should use
SQLCommand.Parameters.Add instead

What if i have 30 inputs on a .aspx page?

So there is no easy way to bind the textfield to the default code that the
visual basic is generating to get the single line SQLCommand.ExecuteNonQuery
to work?

regards
Martin
William (Bill) Vaughn said:
It's not that hard to construct a Parameter object. Your final example
simply uses the defaults. The code generated by the DataAdapterConfiguraiton
wizard uses the all-encompassing constructor you're using--it's overkill for
most applications that don't need updatability.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

message news:[email protected]...
Hi!

I have been trying for several hours now to get this SQLCommand to work...

Below is a snippet... I have cut away some code...

Inherits System.Web.UI.Page
Protected WithEvents SqlConnection As System.Data.SqlClient.SqlConnection
Protected WithEvents tbKundnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents SqlCommand As System.Data.SqlClient.SqlCommand

Private Sub InitializeComponent()
Dim configurationAppSettings As System.Configuration.AppSettingsReader =
New
System.Configuration.AppSettingsReader()
Me.SqlConnection = New System.Data.SqlClient.SqlConnection()
Me.SqlCommand = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection
'
Me.SqlConnection.ConnectionString =
CType(configurationAppSettings.GetValue("SQL.Connection",
GetType(System.String)), String)
'
'SqlCommand
'
Me.SqlCommand.CommandText = "[InsertCustomer]"
Me.SqlCommand.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand.Connection = Me.SqlConnection
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(10, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KundNummer_1",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False,
CType(10, Byte), CType(0, Byte), "tbKundnummer",
System.Data.DataRowVersion.Current, Nothing))
End Sub

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection.Open()
End Sub

Private Sub bnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bnSave.Click
SqlCommand.ExecuteNonQuery() // Executing this will popup a message
InsertCustomer is missing a parameter @Kundnummer_1
End Sub

Why does this not work?

If i use
SqlCommand.Parameters.Add("@Kundnummer_1", tbKundnummer.text)
SqlCommand.ExecuteNonQuery

it will work...

Am i missing something?

Regards

Martin Arvidsson
Click to expand...
Click to expand...
 
Well, of course, if have repetitive field names such as Kundnummer_1,
Kundnummer_2 and so on...you could write a for loop to add the parameters
using an index and concatenating the name of the field as a string.

So basicly what you are telling me is that i should use
SQLCommand.Parameters.Add instead

What if i have 30 inputs on a .aspx page?

So there is no easy way to bind the textfield to the default code that the
visual basic is generating to get the single line
SQLCommand.ExecuteNonQuery to work?

regards
Martin
William (Bill) Vaughn said:
It's not that hard to construct a Parameter object. Your final example
simply uses the defaults. The code generated by the DataAdapterConfiguraiton
wizard uses the all-encompassing constructor you're using--it's overkill for
most applications that don't need updatability.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

message news:[email protected]...
Hi!

I have been trying for several hours now to get this SQLCommand to work...

Below is a snippet... I have cut away some code...

Inherits System.Web.UI.Page
Protected WithEvents SqlConnection As System.Data.SqlClient.SqlConnection
Protected WithEvents tbKundnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents SqlCommand As System.Data.SqlClient.SqlCommand

Private Sub InitializeComponent()
Dim configurationAppSettings As System.Configuration.AppSettingsReader
= New
System.Configuration.AppSettingsReader()
Me.SqlConnection = New System.Data.SqlClient.SqlConnection()
Me.SqlCommand = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection
'
Me.SqlConnection.ConnectionString =
CType(configurationAppSettings.GetValue("SQL.Connection",
GetType(System.String)), String)
'
'SqlCommand
'
Me.SqlCommand.CommandText = "[InsertCustomer]"
Me.SqlCommand.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand.Connection = Me.SqlConnection
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(10, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KundNummer_1",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False,
CType(10, Byte), CType(0, Byte), "tbKundnummer",
System.Data.DataRowVersion.Current, Nothing))
End Sub

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection.Open()
End Sub

Private Sub bnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bnSave.Click
SqlCommand.ExecuteNonQuery() // Executing this will popup a message
InsertCustomer is missing a parameter @Kundnummer_1
End Sub

Why does this not work?

If i use
SqlCommand.Parameters.Add("@Kundnummer_1", tbKundnummer.text)
SqlCommand.ExecuteNonQuery

it will work...

Am i missing something?

Regards

Martin Arvidsson
Click to expand...
Click to expand...
Click to expand...
 
Back
Top