Problem inserting data in a SQL server table

[Dohmaker]

hi everyone, I am been trying to insert data in a table for a while now, and there is an error I keep having and I am unable to find out what I am doing wrong. here is my code:

    Protected Sub ButtonAjouter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonAjouter.Click


        Dim oSQLConn As SqlConnection = New SqlConnection()

        oSQLConn.ConnectionString = "Server=ECAMLMW091;Database=BestEffort_Dev;user=req_priority;password=********;Max Pool Size=400"
        'Open the connection
        Dim cmd As SqlCommand
        Dim rowsAffected As Integer
        Dim strSQL = "INSERT INTO tbl_Availability (Location, [Date of entry],[Date of Availability],Description,GSDC_Name,PA_Name,MGW_Name)VALUES(@Location, @DateEntry,@DateAvailability,@Description,@GSDC_Name,@PA_Name,@MGW_Name)"
        'Out = File.AppendText(FILENAME)

        'Connect to MSSQL Database


        'Open Connection to DB if closed
        If oSQLConn.State = ConnectionState.Closed Then
            oSQLConn.Open()
        End If

        cmd = New SqlCommand(strSQL, oSQLConn)
        With cmd.Parameters
            .Add(New SqlParameter("@Location", SqlDbType.Char, 10))
            .Add(New SqlParameter("@DateEntry", SqlDbType.DateTime, 8))
            .Add(New SqlParameter("@DateAvailability", SqlDbType.Int, 4))
            .Add(New SqlParameter("@Description", SqlDbType.Text, 16))
            .Add(New SqlParameter("@GSDC_Name", SqlDbType.Char, 10))
            .Add(New SqlParameter("@PA_Name", SqlDbType.Char, 10))
            .Add(New SqlParameter("@MGW_Name", SqlDbType.Char, 10))

        End With

        With cmd
            .Parameters.AddWithValue("@Location", DropdownLocation.Text)
            .Parameters.AddWithValue("@DateEntry", Now)
            .Parameters.AddWithValue("@DateAvailability", DropdownNextAvailable.Text)
            .Parameters.AddWithValue("@Description", TextBoxDescription.Text)
            .Parameters.AddWithValue("@GSDC_Name", DropdownGSDC.Text)
            .Parameters.AddWithValue("@PA_Name", DropDownPA.Text)
            .Parameters.AddWithValue("@MGW_Name", DropdownMGW.Text)

        End With
        Try

            rowsAffected = cmd.ExecuteNonQuery()

        Catch Err As Exception
            Debug.WriteLine(Err.Message.ToString)
            MsgBox(Err.Message.ToString)
            MsgBox(strSQL)
            oSQLConn.Close()
        Finally
            oSQLConn.Close()    'Close Connection
            'Out.Close()

        End Try


    End Sub

however with that code I get the following error: The variable name '@Location' has already been declared

I don't get why I get this error. Can anyone help me figure this out?

 

[jmcilhinney]

It's because you're adding all the parameters twice. You add them all once:

        With cmd.Parameters
            .Add(New SqlParameter("@Location", SqlDbType.Char, 10))
            .Add(New SqlParameter("@DateEntry", SqlDbType.DateTime, 8))
            .Add(New SqlParameter("@DateAvailability", SqlDbType.Int, 4))
            .Add(New SqlParameter("@Description", SqlDbType.Text, 16))
            .Add(New SqlParameter("@GSDC_Name", SqlDbType.Char, 10))
            .Add(New SqlParameter("@PA_Name", SqlDbType.Char, 10))
            .Add(New SqlParameter("@MGW_Name", SqlDbType.Char, 10))

        End With

then you add them all again:

        With cmd
            .Parameters.AddWithValue("@Location", DropdownLocation.Text)
            .Parameters.AddWithValue("@DateEntry", Now)
            .Parameters.AddWithValue("@DateAvailability", DropdownNextAvailable.Text)
            .Parameters.AddWithValue("@Description", TextBoxDescription.Text)
            .Parameters.AddWithValue("@GSDC_Name", DropdownGSDC.Text)
            .Parameters.AddWithValue("@PA_Name", DropDownPA.Text)
            .Parameters.AddWithValue("@MGW_Name", DropdownMGW.Text)

        End With

It looks to me like you are under the impression that AddWithValue simply sets the value of an existing parameter but that's not the case. As the name suggests, it adds a new parameter with the specified value. Get rid of that first block of code and just keep the one that uses AddWithValue and you'll be fine.

 

[cjard]

Under .Net 3.0 you probably wouldnt do your data access this [hard, tedious] way in most cases.. Click the DW2 link in my signature and have a read (bearing in mind DW2 is .NET 2 focused.. .NET 3 versions where available are accessible from the menu box in the top right of the MSDN document) )