Help with a datawebform

[bripoin]

I'm working on a Web Application in VB.NET that allows users to view information from our organizations database based on user criteria. I've added a datawebform using the wizard, and copied the database over to my local machine so that I don't mess anything up in the live database (I set my local machine up to run like a SQL Server). However, when I click the button to simply load the entire data, not using criteria, I get the following message:\

 

Login failed for user 'AOGC-561E15\ASPNET'

 

I don't understand why my own machine can't login to itself. Can anyone help me with this? :confused:

[PlausiblyDamp]

You will need to grant the ASPNET account permissions to the database, asp.net web applications run under this account by default.

[bripoin]

Okay...how do I do that?

[PlausiblyDamp]

http://www.xtremedotnettalk.com/showthread.php?t=73762

http://www.xtremedotnettalk.com/showthread.php?t=95095

[bripoin]

Okay. I granted access and now I get this:

 

Cannot open database requested in login 'arrbdms_new'. Login fails.

[bripoin]

Okay...I figured out why the login was failing and got that taken care of. I've even gotten it to pull the information, but I need to be able to enter a number that will match a number stored in a field in the database and pull up the information that coincides with that number entered by the user. Anyone got any suggestions?

[bripoin]

Let me clarify this a bit. I've got a textbox where the user enters a Permit Number. This Permit Number is a unique number used to identify the recordset we are wanting to look at. I've put the text box on the form and I declared a variable (PermitNum) as a String, equal to the text entered in the text box. In the SQLSelectCommand, I have it selecting certain fields with a WHERE Statement that says PermitNo (the name of the column in the database) equals PermitNum (the variable I created). Whenever I run the application, type a valid permit number in the text box, and click the button that starts the procedure, I get the following message:

 

Invalid column name 'PermitNum'

 

I think that it is using the Literal "PermitNum" instead of the value of the variable. How can I make it use the value of the variable instead?

[PlausiblyDamp]

Any chance you could post the code - much easier to see where you might be going wrong if we have something to look at.

[bripoin]

Okay. Here is the code for the button:

Dim PermitNum As String = TextBox1.Text

       Try
           Me.LoadDataSet()
           Me.DataGrid1.SelectedIndex = -1
           Me.DataGrid1.DataBind()
       Catch eLoad As System.Exception
           Me.Response.Write(eLoad.Message)
       End Try

Here is what I used to fill the dataset and all:

Public Sub LoadDataSet()
       'Create a new dataset to hold the records returned from the call to FillDataSet.
       'A temporary dataset is used because filling the existing dataset would
       'require the databindings to be rebound.
       Dim objDataSetTemp As ProductionApplication.ProdAppData
       objDataSetTemp = New ProductionApplication.ProdAppData
       Try
           'Attempt to fill the temporary dataset.
           Me.FillDataSet(objDataSetTemp)
       Catch eFillDataSet As System.Exception
           'Add your error handling code here.
           Throw eFillDataSet
       End Try
       Try
           'Empty the old records from the dataset.
           ProdAppData1.Clear()
           'Merge the records into the main dataset.
           ProdAppData1.Merge(objDataSetTemp)
       Catch eLoadMerge As System.Exception
           'Add your error handling code here.
           Throw eLoadMerge
       End Try

   End Sub
   Public Sub FillDataSet(ByVal dataSet As ProductionApplication.ProdAppData)
       'Turn off constraint checking before the dataset is filled.
       'This allows the adapters to fill the dataset without concern
       'for dependencies between the tables.
       dataSet.EnforceConstraints = False
       Try
           'Open the connection.
           Me.SqlConnection1.Open()
           'Attempt to fill the dataset through the OleDbDataAdapter1.
           Me.SqlDataAdapter1.Fill(dataSet)
       Catch fillException As System.Exception
           'Add your error handling code here.
           Throw fillException
       Finally
           'Turn constraint checking back on.
           dataSet.EnforceConstraints = True
           'Close the connection whether or not the exception was thrown.
           Me.SqlConnection1.Close()
       End Try

   End Sub

And here is the SQLSelect Command:

Me.SqlSelectCommand1.CommandText = "SELECT tblPRUMaster.PruID, tblPRUMaster.PruNumber, tblPRUMaster.PermitNo, tblPRUP" & _
       "roduction.RptDate, tblPRUProduction.NorthOrSouth, tblPRUProduction.OilProd, tblP" & _
       "RUProduction.GasProd, tblPRUProduction.MonthlyAllowableOil, tblPRUProduction.Mon" & _
       "thlyAllowableGas FROM tblPRUMaster INNER JOIN tblPRUProduction ON tblPRUMaster.P" & _
       "ruID = tblPRUProduction.PruID WHERE PermitNo =  PermitNum"
       Me.SqlSelectCommand1.Connection = Me.SqlConnection1

 

 

Anything else you need?

Edited April 28, 2006 by PlausiblyDamp

[PlausiblyDamp]

The problem lies with the SQL - you can't just put a variable inside a string like that.

 

The best way is to use either a stored procedure or a parameterised query - if you search these forums you should find a lot of information about this.

[bripoin]

The problem lies with the SQL - you can't just put a variable inside a string like that.

 

The best way is to use either a stored procedure or a parameterised query - if you search these forums you should find a lot of information about this.

 

I thought that was what I had done when I created the SQLDataAdapter and the DataSet. I will search the forums though to see what I can find. Thanks for all of your help.

[bripoin]

Okay. I did a parameterised query with the following WHERE statement:

 

WHERE tblPRUMaster.PermitNo= '" & Me.TextBox1.Text & "'"

 

When I enter a Permit Number into the text box and click the load button I get a data grid that only has the column headings. However, if I insert the Permit Number into the Where statement directly, I get a datagrid with the headings and data as well. So I know that this permit number has data to go with it.

[PlausiblyDamp]

If the data is a number then you do not need to surround it with '

WHERE tblPRUMaster.PermitNo= " & Me.TextBox1.Text 

 

However concatenating strings like that can be a source of errors and security holes you are much better using a parameterised query instead.

 

something like

Me.SqlSelectCommand1.CommandText = "SELECT tblPRUMaster.PruID, tblPRUMaster.PruNumber, tblPRUMaster.PermitNo, tblPRUP" & _
       "roduction.RptDate, tblPRUProduction.NorthOrSouth, tblPRUProduction.OilProd, tblP" & _
       "RUProduction.GasProd, tblPRUProduction.MonthlyAllowableOil, tblPRUProduction.Mon" & _
       "thlyAllowableGas FROM tblPRUMaster INNER JOIN tblPRUProduction ON tblPRUMaster.P" & _
       "ruID = tblPRUProduction.PruID WHERE (PermitNo =  @PermitNum)"
       Me.SqlSelectCommand1.Connection = Me.SqlConnection1

Me.SelectCommand.Parameters.Add("@PermitNum", SqlDbType.NVarChar)
Me.SelectCommand.Parameters("@PermitNum").Value = TextBox1.Text 

 

I haven't ran the above so it may not be exact - however it should give you the general idea.

Edited April 28, 2006 by PlausiblyDamp

[bripoin]

I tried that and got the following server error:

 

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

[PlausiblyDamp]

I tried that and got the following server error:

 

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

 

I've modified the original post - should work this time, however it still hasn't been tested...

[bripoin]

I've modified the original post - should work this time' date=' however it still hasn't been tested...[/quote']

 

That took care of the error, but I'm still getting a datgrid with just the column headings in my output. I know I must be bugging the soup out of you by now, but I'm new to this and it's driving me crazy. :confused:

[PlausiblyDamp]

Oops - I was converting the wrong post of yours - try changing the SqlType.NVarChar to SqlType.Int and see if that fixes the problem

[bripoin]

Now it says "Input string was not in a correct format."

[PlausiblyDamp]

What is the value of textBox1 when the code errors / fails to work?

[bripoin]

Do you mean what am I typing in? If that's the case I'm using 32534, a Permit Number that I know is in the database.

[PlausiblyDamp]

Me.SqlSelectCommand1.CommandText = "SELECT tblPRUMaster.PruID, tblPRUMaster.PruNumber, tblPRUMaster.PermitNo, " & _
       "tblPRUProduction.RptDate, tblPRUProduction.NorthOrSouth, tblPRUProduction.OilProd, " & _
       "tblPRUProduction.GasProd, tblPRUProduction.MonthlyAllowableOil, " & _
       "tblPRUProduction.MonthlyAllowableGas FROM tblPRUMaster INNER JOIN tblPRUProduction ON " & _
       "tblPRUMaster.PruID = tblPRUProduction.PruID WHERE PermitNo =  @PermitNum"

Me.SqlSelectCommand1.Connection = Me.SqlConnection1


Me.SelectCommand.Parameters.Add("@PermitNum", SqlDbType.Int)
Me.SelectCommand.Parameters("@PermitNum").Value = Integer.Parse(TextBox1.Text)

 

Does the above code make a difference? Also which line is the exception being raised on? If you step through the code in the debugger does TextBox1.Text contain the correct value?

[bripoin]

When it gets to the last line of the code you posted above, it gives me this:

 

Server Error in '/ProductionApplication' Application.

--------------------------------------------------------------------------------

 

Input string was not in a correct format.

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.FormatException: Input string was not in a correct format.

 

Source Error:

 

 

Line 30:

Line 31: Me.SqlSelectCommand1.Parameters.Add("@PermitNum", SqlDbType.Int)

Line 32: Me.SqlSelectCommand1.Parameters("@PermitNum").Value = Integer.Parse(TextBox1.Text)

Line 33: '

Line 34: 'SqlConnection1

 

 

Source File: c:\inetpub\wwwroot\ProductionApplication\WebForm1.aspx.vb Line: 32

 

Stack Trace:

 

 

[FormatException: Input string was not in a correct format.]

System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +0

System.Int32.Parse(String s) +38

ProductionApplication.WebForm1.InitializeComponent() in c:\inetpub\wwwroot\ProductionApplication\WebForm1.aspx.vb:32

ProductionApplication.WebForm1.Page_Init(Object sender, EventArgs e) in c:\inetpub\wwwroot\ProductionApplication\WebForm1.aspx.vb:63

System.Web.UI.Control.OnInit(EventArgs e)

System.Web.UI.Control.InitRecursive(Control namingContainer)

System.Web.UI.Page.ProcessRequestMain()

 

 

 

 

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

[bripoin]

Could it be because I'm using a text box?

[PlausiblyDamp]

Using a textbox shouldn't cause any problems, if you step through the code in a debugger waht is the value of TextBox1.text before the exception is thrown?

[bripoin]

It doesn't get that far so I would assume the Value is null.