Parameter causing error

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

It's been a long night ... Hopefully someone can point out my mistake here.
I'm getting a System.Data.SqlClient.SqlException system error when trying to
pass the parameter to the database. Even substituting a hard-coded value
causes the error. If I drop the parameter, no errors -- but of course, no
parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
 
Earl:

The Valuemember is the FieldName of that combobox, it's highly doubtful
that's your intent here. Replacing it with .SelectedValue is probably what
was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com
 
Bill,

Even using SelectedValue, I still get the same error. Kinda scratching my
head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *
From SalesPayments
Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO
 
I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl said:
Bill,

Even using SelectedValue, I still get the same error. Kinda scratching my
head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *
From SalesPayments
Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO



Earl:

The Valuemember is the FieldName of that combobox, it's highly doubtful
that's your intent here. Replacing it with .SelectedValue is probably
what

was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com


trying

no
Click to expand...
Click to expand...
 
Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))
'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...


Matt Berther said:
I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl said:
Bill,

Even using SelectedValue, I still get the same error. Kinda scratching my
head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *
From SalesPayments
Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO



Earl:

The Valuemember is the FieldName of that combobox, it's highly doubtful
that's your intent here. Replacing it with .SelectedValue is probably
what

was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message

It's been a long night ... Hopefully someone can point out my mistake

here.

I'm getting a System.Data.SqlClient.SqlException system error when
trying

to

pass the parameter to the database. Even substituting a hard-coded value
causes the error. If I drop the parameter, no errors -- but of course,
no

parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl said:
Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))
'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...


I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl said:
Bill,

Even using SelectedValue, I still get the same error. Kinda scratching
Click to expand...
my
head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *
From SalesPayments
Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO





Earl:

The Valuemember is the FieldName of that combobox, it's highly doubtful
that's your intent here. Replacing it with .SelectedValue is probably

what


was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message


It's been a long night ... Hopefully someone can point out my mistake

here.


I'm getting a System.Data.SqlClient.SqlException system error when

trying


to


pass the parameter to the database. Even substituting a hard-coded
Click to expand...
value
causes the error. If I drop the parameter, no errors -- but of course,

no


parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing it
with a stored proc is the construction of the parameter and the SQLCommand
syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it ...

Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************

Matt Berther said:
Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl said:
Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))
'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...


I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:

Bill,

Even using SelectedValue, I still get the same error. Kinda scratching
my

head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *
From SalesPayments
Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO





Earl:

The Valuemember is the FieldName of that combobox, it's highly doubtful
that's your intent here. Replacing it with .SelectedValue is probably

what


was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message


It's been a long night ... Hopefully someone can point out my mistake

here.


I'm getting a System.Data.SqlClient.SqlException system error when

trying


to


pass the parameter to the database. Even substituting a hard-coded
value

causes the error. If I drop the parameter, no errors -- but of course,

no


parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
So, according to your post, this works:

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)

If this works, the replace the above line with:

cmdPayments.Parameters("@CustomerID").Value =
Convert.ToInt32(cmbCustomers.SelectedValue)

Make sure that SelectedValue is not null.

--
Matt Berther
http://www.mattberther.com


Earl said:
Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing it
with a stored proc is the construction of the parameter and the SQLCommand
syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it ...

Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************

Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl said:
Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
Click to expand...
SqlDbType.Int))
'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...




I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:


Bill,

Even using SelectedValue, I still get the same error. Kinda scratching

my


head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *

From SalesPayments

Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO






Earl:

The Valuemember is the FieldName of that combobox, it's highly
Click to expand...
doubtful
that's your intent here. Replacing it with .SelectedValue is probably

what



was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message



It's been a long night ... Hopefully someone can point out my mistake

here.



I'm getting a System.Data.SqlClient.SqlException system error when

trying



to



pass the parameter to the database. Even substituting a hard-coded

value


causes the error. If I drop the parameter, no errors -- but of
Click to expand...
course,
no



parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments =
Click to expand...
cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
 
No ... you may have misunderstood. I meant I hard-coded a value directly
into the stored procedure on the server (WHERE Customer.CustomerID = 1) and
could execute the datareader without a parameter and the stored procedure
would then execute.

The datareader will not execute at all with any reference to the
cmdPayments.Parameters in any of the various permutations I've tried
(including hard-coding the value into the parameter as you suggest).


Matt Berther said:
So, according to your post, this works:

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)

If this works, the replace the above line with:

cmdPayments.Parameters("@CustomerID").Value =
Convert.ToInt32(cmbCustomers.SelectedValue)

Make sure that SelectedValue is not null.

--
Matt Berther
http://www.mattberther.com


Earl said:
Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing it
with a stored proc is the construction of the parameter and the SQLCommand
syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it ....

Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************

Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:

Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...




I would guess that this has to do with your proc parameter being an int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:


Bill,

Even using SelectedValue, I still get the same error. Kinda scratching

my


head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *

From SalesPayments

Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO






Earl:

The Valuemember is the FieldName of that combobox, it's highly
doubtful

that's your intent here. Replacing it with .SelectedValue is probably

what



was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message



It's been a long night ... Hopefully someone can point out my mistake

here.



I'm getting a System.Data.SqlClient.SqlException system error when

trying



to



pass the parameter to the database. Even substituting a hard-coded

value


causes the error. If I drop the parameter, no errors -- but of
course,

no



parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments =
cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Um, I just noticed this...

Try your procedure like this:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer]
@CustomerID int
AS
select * from ...


The way you were declaring ithe @CustomerID variable, I believe that it
was setup for an output parameter.

--
Matt Berther
http://www.mattberther.com


Earl said:
No ... you may have misunderstood. I meant I hard-coded a value directly
into the stored procedure on the server (WHERE Customer.CustomerID = 1) and
could execute the datareader without a parameter and the stored procedure
would then execute.

The datareader will not execute at all with any reference to the
cmdPayments.Parameters in any of the various permutations I've tried
(including hard-coding the value into the parameter as you suggest).


So, according to your post, this works:

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)

If this works, the replace the above line with:

cmdPayments.Parameters("@CustomerID").Value =
Convert.ToInt32(cmbCustomers.SelectedValue)

Make sure that SelectedValue is not null.

--
Matt Berther
http://www.mattberther.com


Earl said:
Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing it
with a stored proc is the construction of the parameter and the
Click to expand...
SQLCommand
syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it
Click to expand...
...
Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************



Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:


Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",

SqlDbType.Int))


'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...





I would guess that this has to do with your proc parameter being an
Click to expand...
int.
Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:



Bill,

Even using SelectedValue, I still get the same error. Kinda
Click to expand...
scratching
my



head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *

From SalesPayments


Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO







Earl:

The Valuemember is the FieldName of that combobox, it's highly

doubtful


that's your intent here. Replacing it with .SelectedValue is
Click to expand...
probably
what




was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message




It's been a long night ... Hopefully someone can point out my
Click to expand...
mistake
here.




I'm getting a System.Data.SqlClient.SqlException system error when

trying




to




pass the parameter to the database. Even substituting a hard-coded

value



causes the error. If I drop the parameter, no errors -- but of

course,


no




parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments =

cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Hehehe ... very good Matt!! That was the bomb ... thank you very much.

Matt Berther said:
Um, I just noticed this...

Try your procedure like this:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer]
@CustomerID int
AS
select * from ...


The way you were declaring ithe @CustomerID variable, I believe that it
was setup for an output parameter.

--
Matt Berther
http://www.mattberther.com


Earl said:
No ... you may have misunderstood. I meant I hard-coded a value directly
into the stored procedure on the server (WHERE Customer.CustomerID = 1) and
could execute the datareader without a parameter and the stored procedure
would then execute.

The datareader will not execute at all with any reference to the
cmdPayments.Parameters in any of the various permutations I've tried
(including hard-coding the value into the parameter as you suggest).


So, according to your post, this works:

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)

If this works, the replace the above line with:

cmdPayments.Parameters("@CustomerID").Value =
Convert.ToInt32(cmbCustomers.SelectedValue)

Make sure that SelectedValue is not null.

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:

Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing it
with a stored proc is the construction of the parameter and the
SQLCommand

syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it
...

Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************



Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:


Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",

SqlDbType.Int))


'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...





I would guess that this has to do with your proc parameter being an
int.

Try using one of the parameter.Add overloads that let you specify the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:



Bill,

Even using SelectedValue, I still get the same error. Kinda
scratching

my



head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *

From SalesPayments


Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO







Earl:

The Valuemember is the FieldName of that combobox, it's highly

doubtful


that's your intent here. Replacing it with .SelectedValue is
probably

what




was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message




It's been a long night ... Hopefully someone can point out my
mistake

here.




I'm getting a System.Data.SqlClient.SqlException system error when

trying




to




pass the parameter to the database. Even substituting a hard-coded

value



causes the error. If I drop the parameter, no errors -- but of

course,


no




parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments =

cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Glad to help...

--
Matt Berther
http://www.mattberther.com


Earl said:
Hehehe ... very good Matt!! That was the bomb ... thank you very much.

Um, I just noticed this...

Try your procedure like this:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer]
@CustomerID int
AS
select * from ...


The way you were declaring ithe @CustomerID variable, I believe that it
was setup for an output parameter.

--
Matt Berther
http://www.mattberther.com


Earl said:
No ... you may have misunderstood. I meant I hard-coded a value directly
into the stored procedure on the server (WHERE Customer.CustomerID = 1)
Click to expand...
and
could execute the datareader without a parameter and the stored
Click to expand...
procedure
would then execute.

The datareader will not execute at all with any reference to the
cmdPayments.Parameters in any of the various permutations I've tried
(including hard-coding the value into the parameter as you suggest).




So, according to your post, this works:

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
SqlDbType.Int))

'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments = cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)

If this works, the replace the above line with:

cmdPayments.Parameters("@CustomerID").Value =
Convert.ToInt32(cmbCustomers.SelectedValue)

Make sure that SelectedValue is not null.

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:


Man I wish! Nothing more than the exception and "system error".

I tried this with an ad-hoc query (below) and get the results with no
problem, no errors. Notice the only difference between this and doing
Click to expand...
it
with a stored proc is the construction of the parameter and the

SQLCommand


syntax.

Also hard-coded a value of 1 into the @CustomerID stored proc and it
executes and returns the record indicated.

Sure looks like a syntax error on the Parameter, but I haven't found it

...


Thanks for riding along.

**********************************************************************

Dim strPaymentSelect As String = "Select * FROM " & _
"SalesPayments " & _
"Inner Join Sales " & _
"INNER join CustomerQuotes " & _
"Inner Join Customers " & _
"On Customers.CustomerID = CustomerQuotes.CustomerID " & _
"On Sales.QuoteID = CustomerQuotes.QuoteID " & _
"On Sales.SalesID = SalesPayments.SalesID " & _
"WHERE Customers.CustomerID = " & cmbCustomers.SelectedValue

Dim cmdPayments As New SqlCommand(strPaymentSelect, cnSQLServer)
Dim drPayments As SqlDataReader
cnSQLServer.Open()
drPayments = cmdPayments.ExecuteReader()

**********************************************************************




Does the SqlException have anything in the Message property that might
help us debug?

--
Matt Berther
http://www.mattberther.com


Earl <brikshoe<at>comcast wrote:



Thanks Matt ... I've done that, same error

cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",

SqlDbType.Int))



'hard-coding a value for the moment
cmdPayments.Parameters("@CustomerID").Value = 1
drPayments =
Click to expand...
cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
...






I would guess that this has to do with your proc parameter being an

int.


Try using one of the parameter.Add overloads that let you specify
Click to expand...
the
SqlDbType.

--
Matt Berther
http://www.mattberther.com

Earl <brikshoe<at>comcast wrote:




Bill,

Even using SelectedValue, I still get the same error. Kinda

scratching


my




head because this looks too straightforward ....

Here is the stored procedure:

CREATE PROCEDURE [dbo].[GetPaymentHistoryByCustomer] AS
Declare @CustomerID int

Select *

From SalesPayments



Inner Join Sales
Inner Join CustomerQuotes
Inner Join Customers
On Customers.CustomerID = CustomerQuotes.CustomerID
On Sales.QuoteID = CustomerQuotes.QuoteID
On Sales.SalesID = SalesPayments.SalesID
WHERE
Customers.CustomerID = @CustomerID
GO



"William Ryan eMVP" <[email protected]> wrote in
Click to expand...
message
Earl:

The Valuemember is the FieldName of that combobox, it's highly

doubtful



that's your intent here. Replacing it with .SelectedValue is

probably


what





was originallly intended.

If not let me know.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

"Earl comcast net>" <brikshoe<at.> wrote in message





It's been a long night ... Hopefully someone can point out my

mistake


here.





I'm getting a System.Data.SqlClient.SqlException system error
Click to expand...
when
trying





to





pass the parameter to the database. Even substituting a
Click to expand...
hard-coded
value




causes the error. If I drop the parameter, no errors -- but of

course,



no





parameter either.

Dim cmdPayments As New SqlCommand
Dim drPayments As SqlDataReader
cnSQLServer.Open()
cmdPayments = cnSQLServer.CreateCommand
cmdPayments.CommandType = CommandType.StoredProcedure
cmdPayments.CommandText = "GetPaymentHistoryByCustomer"
'this line causing the error
cmdPayments.Parameters.Add(New SqlParameter("@CustomerID",
cmbCustomers.ValueMember))
drPayments =

cmdPayments.ExecuteReader(CommandBehavior.CloseConnection)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top