Help with a datawebform

B

bripoin

Active member
Joined
Apr 26, 2006
Messages
25
Im working on a Web Application in VB.NET that allows users to view information from our organizations database based on user criteria. Ive added a datawebform using the wizard, and copied the database over to my local machine so that I dont 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 dont understand why my own machine cant login to itself. Can anyone help me with this? :confused:
 
Okay. I granted access and now I get this:

Cannot open database requested in login arrbdms_new. Login fails.
 
Okay...I figured out why the login was failing and got that taken care of. Ive 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?
 
Let me clarify this a bit. Ive 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. Ive 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?
 
Okay. Here is the code for the button:
Code: 
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:
Code: 
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:
Code: 
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?
 
Last edited by a moderator:
The problem lies with the SQL - you cant 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.
 
PlausiblyDamp said:
The problem lies with the SQL - you cant 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.
Click to expand...

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.
 
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.
 
If the data is a number then you do not need to surround it with
Code: 
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
Code: 
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 havent ran the above so it may not be exact - however it should give you the general idea.
 
Last edited by a moderator:
I tried that and got the following server error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.
 
bripoin said:
I tried that and got the following server error:

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

Ive modified the original post - should work this time, however it still hasnt been tested...
 
PlausiblyDamp said:
Ive modified the original post - should work this time, however it still hasnt been tested...
Click to expand...

That took care of the error, but Im 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 Im new to this and its driving me crazy. :confused:
 
Do you mean what am I typing in? If thats the case Im using 32534, a Permit Number that I know is in the database.
 
Back
Top