Help with simple insert

[mThomas]

I am new to ASP.NET. I have been looking at several tutorials. I can do some pretty simple stuff except get an insert to work. Ok I can connect but can not do much else with a MS SQL database.

There has got to be a simpler way. Here is what I've been trying.

<%@ Page Language="VB" %>
<script runat="server">

        Function MyInsertState(ByVal state As String) As Integer
            Dim connectionString As String = "server='myserverip'; user id='myid'; password='mypass'; database='CentralDatabase'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

            Dim queryString As String = "INSERT INTO [States] ([state]) VALUES (@state)"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection

            Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
            dbParam_state.ParameterName = "@state"
            dbParam_state.Value = state
            dbParam_state.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_state)

            Dim rowsAffected As Integer = 0
            dbConnection.Open
            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close
            End Try

            Return rowsAffected
        End Function


        sub Page_Load(obj as object, e as eventargs)

        dim thestate as string = "new"

            MyInsertState(thestate)

        end sub

I am getting an "Server Error in '/' Application." If I take the sub out, the code runs without error, but of course does not do the insert.

Any help would be greatly appreciated.

tia... mike

 

[jbenson001]

You will have to trace through your sub line by line to find the error.

 

[BoulderBum]

Can you give more detailed error information? It probably says something else in the Exception.

 

[ca8msm]

Yeah, instead of:

            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close
            End Try

add a catch statement to catch the exception e.g.

            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Catch ex As Exception
                ' step through the code and look at the exception here
            Finally
                dbConnection.Close
            End Try

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[mThomas]

It turns out my issues were in the server name. I had to include \SQLSERVER after the server IP and was able, after some minor tweaking, to get what I wanted done. Who ever installed the sql server added that to the group name.

mike