web form not working please help

[myth512]

I have been trying to create a form to put data into a access database. I had the web host set up a ODBC connection for me. I use web matrix wizard to create the code and changed the connection string for the ODBC connection. I know I have to change a few more things but am not sure what to change.
here is the code:

Function work1() As System.Data.DataSet
            
     dim objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "dsn=crf"
    objConn.Open

            Dim queryString As String = "SELECT [requests].* FROM [requests]"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = objconn

            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)

            Return dataSet
        End Function

Function work2(ByVal name As String, ByVal company As String, ByVal street0 As String, ByVal street1 As String, ByVal city As String, ByVal state As String, ByVal zip As String) As Integer

     dim objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "dsn=crf"
    objConn.Open

 Dim queryString As String = "INSERT INTO [requests] ([name], [company], [street0], [street1], [city], [state],"& _
    " [zip]) VALUES (@name, @company, @street0, @street1, @city, @state, @zip)"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = objconn

            Dim dbParam_name As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_name.ParameterName = "@name"
            dbParam_name.Value = name
            dbParam_name.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_name)
            Dim dbParam_company As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_company.ParameterName = "@company"
            dbParam_company.Value = company
            dbParam_company.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_company)
            Dim dbParam_street0 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_street0.ParameterName = "@street0"
            dbParam_street0.Value = street0
            dbParam_street0.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_street0)
            Dim dbParam_street1 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_street1.ParameterName = "@street1"
            dbParam_street1.Value = street1
            dbParam_street1.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_street1)
            Dim dbParam_city As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_city.ParameterName = "@city"
            dbParam_city.Value = city
            dbParam_city.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_city)
            Dim dbParam_state As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_state.ParameterName = "@state"
            dbParam_state.Value = state
            dbParam_state.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_state)
            Dim dbParam_zip As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_zip.ParameterName = "@zip"
            dbParam_zip.Value = zip
            dbParam_zip.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_zip)

            Dim rowsAffected As Integer = 0
           objconn.open
           Try
               rowsAffected = dbCommand.ExecuteNonQuery
              Catch
       msgLabel.Text = "Database Error"
      msgLabel.Visible = true
         Finally
               objconn.Close
            End Try

           Return rowsAffected
        End Function' Insert page code here

Sub submit_Click(sender As Object, e As EventArgs)

    If tname.Text = "" Or tcompany.Text = "" Or tstreet0.Text = "" Or tcity.Text = "" Or tstate.text = "" Or tzip.text = "" Then

    msgLabel.Text = "One or more fields are empty. <br>Please complete all fields."
    msgLabel.Visible = true

    ElseIf work2(tname.Text, tcompany.Text, tstreet0.text, tstreet1.text, tcity.Text, tstate.text, tzip.text) > 0 Then
    msgLabel.Text = tname.Text & " was added successfully"
    msgLabel.Visible = True

    End If

    End Sub

Any comments would be a big help as creating web pages is not my main job. Thank You.

[Paladine]

Ok, I looked over the code, and just in general it looks to be fine. What is not working? Are you gettting an error message ( aside from your catch block error message)?

And please when you post code on forums in general use "["code"]""["/code"]" blocks to enclose that code. Makes it easier to read the flow.

Without an idea of what is not working, I can't really say what to check or fix.

You have confirmed that your DSN is working?

[myth512]

When you click the submit button it comes up with an error. here is what I get:

Server Error in '/' Application.
Specified cast is not valid.
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.InvalidCastException: Specified cast is not valid.

Source Error:

Line 31: Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
Line 32: dbCommand.CommandText = queryString
Line 33: dbCommand.Connection = objconn
Line 34:
Line 35: Dim dbParam_name As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter

Source File: E:\inetpub\wwwroot\drcomfortshoes\good2.aspx Line: 33

I confirmed that the connection string is correct. I changed the connection string to work with the ODBC connection as the other way of putting the full path would not work.

[myth512]

The page is at www.drcomfortshoes.com/good2.aspx

[Paladine]

OK very quickly, you are opening a connection and trying to convert that (CAST it actually) to a OleDbCommand Object.

Thus,

Change these lines

objConn.Open

 Dim queryString As String = "INSERT INTO [requests] ([name], [company], [street0], [street1], [city], [state],"& _
	" [zip]) VALUES (@name, @company, @street0, @street1, @city, @state, @zip)"
			Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
			dbCommand.CommandText = queryString
			dbCommand.Connection = objconn

to be this

'objConn.Open

 Dim queryString As String = "INSERT INTO [requests] ([name], [company], [street0], [street1], [city], [state],"& _
	" [zip]) VALUES (@name, @company, @street0, @street1, @city, @state, @zip)"
			Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
			dbCommand.CommandText = queryString
			dbCommand.Connection = objconn
			
dbCommand.Connection.Open()

Hope this helps

[myth512]

I changed the code and I am still getting an error. If you go to the web page and click submit you can see the error I get. If you have anyother ideas please share.
Thank You

[Paladine]

Haha, sorry, I wrote that at like 5 am this morning.

Reviewing your code the source of the error is this line

dim objConn = Server.CreateObject("ADODB.Connection")

Because this objConn is not declared as anything. It is only made equal to something, it needs to be set.

so:

Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")

The other thing to keep in mind is you should set this in your Global.asa file (suggestion):

|||||||||||| IN GLOBAL.ASA  |||||||||||||||||||||
Sub Application_OnStart()
    ' Replace 'myusername' in the below with your actual user name
    Application("DB_CONNECTION_STRING")   = "DSN=datasource_name;"
    Application("DB_CONNECTION_LOGIN")    = "" ' Optional
    Application("DB_CONNECTION_PASSWORD") = "" ' Optional
End Sub

||||||||||||||||  IN ASP.NET CODE BEHIND or SCRIPT   ||||||||||||||||||||||||
dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("DB_CONNECTION_STRING"),Application("DB_CONNECTION_LOGIN"),Application("DB_CONNECTION_PASSWORD")
' do something
Conn.Close

Hope this helps, and I am sorry for my first reply, as I said I was up at 5 am so no thinking too clearly.

Set Conn = nothing

[myth512]

I changed the code to what you said. here is the code I am using for the connection string:
"["code"]"
dim conn
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("DB_CONNECTION_STRING")
"["/code"]"

I still get an error. If you go to the site you can see the error. Its a problem saying it does not support let and set commands.

[Paladine]

Hmm, well I will have to ask why you need to use a DSN? But anyhow I find this odd, but lets try one more thing,

Dim objConn as OdbcConnection = New OdbcConnection ("DSN=crf;")
objConn.Open()