Form questions

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Please let me know if there is a more appropriate NG for this question.

I am building my first ASPX application.

I would like to learn how to correctly use the .NET environment to perform
a basic sql query.

I have created an SqlDataAdpater, SQL Connection, and generated a dataset.

It looks like all of my SQLCommands have been automatically generated.

I can successfully connect to my SQL server.

I have a single textbox and submit button on the form. I would like to
enter a string in the textbox and query the Name_Last column for that
string.

I am under the impression that I need to use the parameters collection to
map the textbox value to the SQL query.

I have:

select name_last, name_first from persons where name_last = @LastName

for my SQLSelectCommand.

I am not sure how to map @LastName to textbox1.text.

Any ideas?

Aaron
 
In C

SqlCommand sqlCmd = new SqlCommand("select name_last, name_first from persons where name_last = @LastName", yourconnectionobject)

sqlCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.Varchar, 10); //change type and lenght based on your paramete
sqlCmd.Parameters["@LastName"].Value = textbox1.text.Trim()
...

Suresh

----- Aaron wrote: ----

Please let me know if there is a more appropriate NG for this question

I am building my first ASPX application

I would like to learn how to correctly use the .NET environment to perform
a basic sql query

I have created an SqlDataAdpater, SQL Connection, and generated a dataset

It looks like all of my SQLCommands have been automatically generated

I can successfully connect to my SQL server

I have a single textbox and submit button on the form. I would like to
enter a string in the textbox and query the Name_Last column for that
string.

I am under the impression that I need to use the parameters collection to
map the textbox value to the SQL query

I have

select name_last, name_first from persons where name_last = @LastNam

for my SQLSelectCommand

I am not sure how to map @LastName to textbox1.text

Any ideas

Aaro
 
I have converted that C# code to VB. Here is the code I am using:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sqlCmd As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("select name_last, name_first from persons
where name_last = @LastName", SqlConnection1)
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@LastName", SqlDbType.NVarChar, 30)) 'change type and length based on
your parameter
sqlCmd.Parameters("@LastName").Value = TextBox1.Text.Trim()
SqlDataAdapter1.Fill(DataSet11)


I get the following error (Error on Line 174) when I click the submit
button after entering a string in the text box:

Prepared statement '(@LastName varchar(30))SELECT Person_ID, Salutation,
Name_Full, ' expects parameter @LastName, which was not supplied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Prepared statement
'(@LastName varchar(30))SELECT Person_ID, Salutation, Name_Full, ' expects
parameter @LastName, which was not supplied.

Source Error:


Line 172: sqlCmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName", SqlDbType.NVarChar, 30))
'change type and length based on your parameter
Line 173: sqlCmd.Parameters("@LastName").Value = TextBox1.Text.Trim
()
Line 174: SqlDataAdapter1.Fill(DataSet11)
Line 175: End Sub
Line 176:End Class
 
My new code looks like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sqlCmd As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("select name_last, name_first from
person where name_last = @LastName", SqlConnection1)
Dim SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter = New
System.Data.SqlClient.SqlDataAdapter(sqlCmd)
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@LastName", SqlDbType.NVarChar, 30)) 'change type and length based on
your parameter
sqlCmd.Parameters("@LastName").Value = TextBox1.Text.Trim()
SqlDataAdapter1.Fill(DataSet11)
End Sub

When I run the code, I get the following in Profiler. I know that it is
hitting the server, but I don't think that the query is actually running
successfully. I am not using stored procedures.

sp_executesql N'select name_last, name_first from persons where name_last
= @LastName', N'@LastName nvarchar(30)', N'benage'

If I copy this into Query Analyzer I am prompted to define the variable.
Normally I would copy the query from profiler into QA to determine if the
syntax is correct and the resultant recordset is what I want. I can't do
that in this case.

I do not get any errors in IE.

Thanks for your help,
Aaron
 
Your code looks ok to me

You should be getting the following in the profiler when you trace the call
sp_executesql N'select name_last, name_first from persons where name_last = @LastName', N'@LastName nvarchar(30)', @LastName = 'benage

One more thing. Not sure if it'll help
Set the following
sqlCmd.CommandType = CommandType.Tex

Suresh

----- Aaron wrote: ----

My new code looks like this

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Clic
Dim sqlCmd As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("select name_last, name_first from
person where name_last = @LastName", SqlConnection1
Dim SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter = New
System.Data.SqlClient.SqlDataAdapter(sqlCmd
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParamete
("@LastName", SqlDbType.NVarChar, 30)) 'change type and length based on
your paramete
sqlCmd.Parameters("@LastName").Value = TextBox1.Text.Trim(
SqlDataAdapter1.Fill(DataSet11
End Su

When I run the code, I get the following in Profiler. I know that it is
hitting the server, but I don't think that the query is actually running
successfully. I am not using stored procedures

sp_executesql N'select name_last, name_first from persons where name_last
= @LastName', N'@LastName nvarchar(30)', N'benage

If I copy this into Query Analyzer I am prompted to define the variable.
Normally I would copy the query from profiler into QA to determine if the
syntax is correct and the resultant recordset is what I want. I can't do
that in this case

I do not get any errors in IE

Thanks for your help
Aaro
 
Back
Top