Programming Forums

Programming Forums (http://www.programmingforums.org/forumindex.php)
-   Visual Basic .NET (http://www.programmingforums.org/forum19.html)
-   -   Insert command just doesn't work. (http://www.programmingforums.org/showthread.php?t=15198)

SydneyMcConnell Feb 16th, 2008 1:08 PM
Insert command just doesn't work.
 
Hey, all.
Been a while since I've posted -- So busy XP

Anyway.

I have a giant insert statement set up, using sqlParameters added to a sqlCommand in VB.Net.
I instantiate the parameters, add them to the command, and then set their values.
At the end I do a cmd.ExecuteNonQuery() to run the insert statement. It does not throw an error, however it also doesn't put the data in the database.

What's going on here? Any help is much appreciated.

Ghost Feb 16th, 2008 1:20 PM
Re: Insert command just doesn't work.
 
Show me the CODE!

SydneyMcConnell Feb 16th, 2008 1:22 PM
Re: Insert command just doesn't work.
 
:
Private Sub frmCustomers_Add(ByVal adding As Boolean)
        If adding = False Then
            frwRoutines.unBindData(Me)
            navButtons.DataSet = Nothing
            Dim strSql As String = "INSERT INTO tblCustomers(FIRSTNAME, LASTNAME, COMPANY, ADDRESS1, ADDRESS2, CITY, STATE, ZIPCODE, OWNERSNAME, MAILADDRESS, MAILCITY, MAILSTATE, MAILZIP, SERCON, HOMEPHONE, CELLPHONE, WORKPHONE, FAXNUMBER, SENDMAIL, STATUSDESC, CODE, FURNACEMAKE, FURNMODEL, FURNSERIALNUM, FURNINST, ACMAKE, ACMODEL, ACSERIALNUM, ACINST, AIRCLEANER, AIRCLEANERINST, HUMIDIFIER, HUMIDIFIERINST, HWTMAKE, HWTMODEL, HWTINSTALL, ATTICFAN, FANINSTALL, FURNACEMAKE2, FURNMODEL2, FURNSERIAL2, FURNINSTALL2, ACMAKE2, ACMODEL2, ACSERIAL2, ACINSTALL2, AIRCLNR2, AIRCLNRINSTALL2, HUM2, HUMINSTALL2, COMMENTS) VALUES(@FIRSTNAME, @LASTNAME, @COMPANY, @ADDRESS1, @ADDRESS2, @CITY, @STATE, @ZIPCODE, @OWNERSNAME, @MAILADDRESS, @MAILCITY, @MAILSTATE, @MAILZIP, @SERCON, @HOMEPHONE, @CELLPHONE, @WORKPHONE, @FAXNUMBER, @SENDMAIL, @STATUSDESC, @CODE, @FURNACEMAKE, @FURNMODEL, @FURNSERIALNUM, @FURNINST, @ACMAKE, @ACMODEL, @ACSERIALNUM, @ACINST, @AIRCLEANER, @AIRCLEANERINST, @HUMIDIFIER, @HUMIDIFIERINST, @HWTMAKE, @HWTMODEL, @HWTINSTALL, @ATTICFAN, @FANINSTALL, @FURNACEMAKE2, @FURNMODEL2, @FURNSERIAL2, @FURNINSTALL2, @ACMAKE2, @ACMODEL2, @ACSERIAL2, @ACINSTALL2, @AIRCLNR2, @AIRCLNRINSTALL2, @HUM2, @HUMINSTALL2, @COMMENTS)"
            Dim cmd As New SqlClient.SqlCommand(strSql, cnConnection)

            Dim parFirstName As New SqlClient.SqlParameter("@FirstName", SqlDbType.VarChar)
            Dim parLastName As New SqlClient.SqlParameter("@LastName", SqlDbType.VarChar)
            Dim parCompany As New SqlClient.SqlParameter("@Company", SqlDbType.VarChar)
            Dim parAddress1 As New SqlClient.SqlParameter("@Address1", SqlDbType.VarChar)
            Dim parAddress2 As New SqlClient.SqlParameter("@Address2", SqlDbType.VarChar)
            Dim parCity As New SqlClient.SqlParameter("@City", SqlDbType.VarChar)
            Dim parState As New SqlClient.SqlParameter("@State", SqlDbType.VarChar)
            Dim parZipCode As New SqlClient.SqlParameter("@ZipCode", SqlDbType.VarChar)
            Dim parOwnersName As New SqlClient.SqlParameter("@OwnersName", SqlDbType.VarChar)
            Dim parMailAddress As New SqlClient.SqlParameter("@MailAddress", SqlDbType.VarChar)
            Dim parMailCity As New SqlClient.SqlParameter("@MailCity", SqlDbType.VarChar)
            Dim parMailState As New SqlClient.SqlParameter("@MailState", SqlDbType.VarChar)
            Dim parMailZip As New SqlClient.SqlParameter("@MailZip", SqlDbType.VarChar)
            Dim parSerCon As New SqlClient.SqlParameter("@SerCon", SqlDbType.VarChar)
            Dim parHomePhone As New SqlClient.SqlParameter("@HomePhone", SqlDbType.VarChar)
            Dim parCellPhone As New SqlClient.SqlParameter("@CellPhone", SqlDbType.VarChar)
            Dim parWorkPhone As New SqlClient.SqlParameter("@WorkPhone", SqlDbType.VarChar)
            Dim parFaxNumber As New SqlClient.SqlParameter("@FaxNumber", SqlDbType.VarChar)
            Dim parSendMail As New SqlClient.SqlParameter("@SendMail", SqlDbType.Bit)
            Dim parStatusDesc As New SqlClient.SqlParameter("@StatusDesc", SqlDbType.VarChar)
            Dim parCode As New SqlClient.SqlParameter("@Code", SqlDbType.VarChar)
            Dim parFurnaceMake As New SqlClient.SqlParameter("@FurnaceMake", SqlDbType.VarChar)
            Dim parFurnModel As New SqlClient.SqlParameter("@FurnModel", SqlDbType.VarChar)
            Dim parFurnSerialNum As New SqlClient.SqlParameter("@FurnSerialNum", SqlDbType.VarChar)
            Dim parFurnInst As New SqlClient.SqlParameter("@FurnInst", SqlDbType.VarChar)
            Dim parAcMake As New SqlClient.SqlParameter("@AcMake", SqlDbType.VarChar)
            Dim parAcModel As New SqlClient.SqlParameter("@AcModel", SqlDbType.VarChar)
            Dim parAcSerialNum As New SqlClient.SqlParameter("@AcSerialNum", SqlDbType.VarChar)
            Dim parAcInst As New SqlClient.SqlParameter("@AcInst", SqlDbType.VarChar)
            Dim parAirCleaner As New SqlClient.SqlParameter("@AirCleaner", SqlDbType.VarChar)
            Dim parAirCleanerInst As New SqlClient.SqlParameter("@AirCleanerInst", SqlDbType.VarChar)
            Dim parHumidifier As New SqlClient.SqlParameter("@Humidifier", SqlDbType.VarChar)
            Dim parHumidifierInst As New SqlClient.SqlParameter("@HumidifierInst", SqlDbType.VarChar)
            Dim parHwtMake As New SqlClient.SqlParameter("@HwtMake", SqlDbType.VarChar)
            Dim parHwtModel As New SqlClient.SqlParameter("@HwtModel", SqlDbType.VarChar)
            Dim parHwtInstall As New SqlClient.SqlParameter("@HwtInstall", SqlDbType.VarChar)
            Dim parAtticFan As New SqlClient.SqlParameter("@AtticFan", SqlDbType.VarChar)
            Dim parFanInstall As New SqlClient.SqlParameter("@FanInstall", SqlDbType.VarChar)
            Dim parFurnaceMake2 As New SqlClient.SqlParameter("@FurnaceMake2", SqlDbType.VarChar)
            Dim parFurnModel2 As New SqlClient.SqlParameter("@FurnModel2", SqlDbType.VarChar)
            Dim parFurnSerial2 As New SqlClient.SqlParameter("@FurnSerial2", SqlDbType.VarChar)
            Dim parFurnInstall2 As New SqlClient.SqlParameter("@FurnInstall2", SqlDbType.VarChar)
            Dim parAcMake2 As New SqlClient.SqlParameter("@AcMake2", SqlDbType.VarChar)
            Dim parAcModel2 As New SqlClient.SqlParameter("@AcModel2", SqlDbType.VarChar)
            Dim parAcSerial2 As New SqlClient.SqlParameter("@AcSerial2", SqlDbType.VarChar)
            Dim parAcInstall2 As New SqlClient.SqlParameter("@AcInstall2", SqlDbType.VarChar)
            Dim parAirClnr2 As New SqlClient.SqlParameter("@AirClnr2", SqlDbType.VarChar)
            Dim parAirClnrInstall2 As New SqlClient.SqlParameter("@AirClnrInstall2", SqlDbType.VarChar)
            Dim parHum2 As New SqlClient.SqlParameter("@Hum2", SqlDbType.VarChar)
            Dim parHumInstall2 As New SqlClient.SqlParameter("HumInstall2", SqlDbType.VarChar)
            Dim parComments As New SqlClient.SqlParameter("@Comments", SqlDbType.VarChar)

            cmd.Parameters.Add(parFirstName)
            cmd.Parameters.Add(parLastName)
            cmd.Parameters.Add(parCompany)
            cmd.Parameters.Add(parAddress1)
            cmd.Parameters.Add(parAddress2)
            cmd.Parameters.Add(parCity)
            cmd.Parameters.Add(parState)
            cmd.Parameters.Add(parZipCode)
            cmd.Parameters.Add(parOwnersName)
            cmd.Parameters.Add(parMailAddress)
            cmd.Parameters.Add(parMailCity)
            cmd.Parameters.Add(parMailState)
            cmd.Parameters.Add(parMailZip)
            cmd.Parameters.Add(parSerCon)
            cmd.Parameters.Add(parHomePhone)
            cmd.Parameters.Add(parCellPhone)
            cmd.Parameters.Add(parWorkPhone)
            cmd.Parameters.Add(parFaxNumber)
            cmd.Parameters.Add(parSendMail)
            cmd.Parameters.Add(parStatusDesc)
            cmd.Parameters.Add(parCode)
            cmd.Parameters.Add(parFurnaceMake)
            cmd.Parameters.Add(parFurnModel)
            cmd.Parameters.Add(parFurnSerialNum)
            cmd.Parameters.Add(parFurnInst)
            cmd.Parameters.Add(parAcMake)
            cmd.Parameters.Add(parAcModel)
            cmd.Parameters.Add(parAcSerialNum)
            cmd.Parameters.Add(parAcInst)
            cmd.Parameters.Add(parAirCleaner)
            cmd.Parameters.Add(parAirCleanerInst)
            cmd.Parameters.Add(parHumidifier)
            cmd.Parameters.Add(parHumidifierInst)
            cmd.Parameters.Add(parHwtMake)
            cmd.Parameters.Add(parHwtModel)
            cmd.Parameters.Add(parHwtInstall)
            cmd.Parameters.Add(parAtticFan)
            cmd.Parameters.Add(parFanInstall)
            cmd.Parameters.Add(parFurnaceMake2)
            cmd.Parameters.Add(parFurnModel2)
            cmd.Parameters.Add(parFurnSerial2)
            cmd.Parameters.Add(parFurnInstall2)
            cmd.Parameters.Add(parAcMake2)
            cmd.Parameters.Add(parAcModel2)
            cmd.Parameters.Add(parAcSerial2)
            cmd.Parameters.Add(parAcInstall2)
            cmd.Parameters.Add(parAirClnr2)
            cmd.Parameters.Add(parAirClnrInstall2)
            cmd.Parameters.Add(parHum2)
            cmd.Parameters.Add(parHumInstall2)
            cmd.Parameters.Add(parComments)

            parFirstName.Value = txtFirstName.Text
            parLastName.Value = txtLastName.Text
            parCompany.Value = txtCompany.Text
            parAddress1.Value = txtAddress1.Text
            parAddress2.Value = txtAddress2.Text
            parCity.Value = txtCity.Text
            parState.Value = txtState.Text
            parZipCode.Value = txtZipCode.Text
            parOwnersName.Value = txtOwnersName.Text
            parMailAddress.Value = txtMailAddress.Text
            parMailCity.Value = txtMailCity.Text
            parMailState.Value = txtMailState.Text
            parMailZip.Value = txtMailZip.Text
            parSerCon.Value = txtSerCon.Text
            parHomePhone.Value = txtHomePhone.Text
            parCellPhone.Value = txtCellPhone.Text
            parWorkPhone.Value = txtWorkPhone.Text
            parFaxNumber.Value = txtFaxNumber.Text
            parSendMail.Value = chkSendMail.Checked
            parStatusDesc.Value = cboStatus.SelectedValue.ToString
            parCode.Value = cboCode.SelectedValue.ToString
            parFurnaceMake.Value = txtFurnaceMake.Text
            parFurnModel.Value = txtFurnModel.Text
            parFurnSerialNum.Value = txtFurnSerialNum.Text
            parFurnInst.Value = txtFurnInst.Text
            parAcMake.Value = txtACMake.Text
            parAcModel.Value = txtACModel.Text
            parAcSerialNum.Value = txtACSerialNum.Text
            parAcInst.Value = txtACInst.Text
            parAirCleaner.Value = txtAirCleaner.Text
            parAirCleanerInst.Value = txtAirCleanerInst.Text
            parHumidifier.Value = txtHumidifier.Text
            parHumidifierInst.Value = txtHumidifierInst.Text
            parHwtMake.Value = txtHWTMake.Text
            parHwtModel.Value = txtHWTModel.Text
            parHwtInstall.Value = txtHWTInstall.Text
            parAtticFan.Value = txtAtticFan.Text
            parFanInstall.Value = txtFanInstall.Text
            parFurnaceMake2.Value = txtFurnaceMake2.Text
            parFurnModel2.Value = txtFurnModel2.Text
            parFurnSerial2.Value = txtFurnSerial2.Text
            parFurnInstall2.Value = txtFurnInstall2.Text
            parAcMake2.Value = txtACMake.Text
            parAcModel2.Value = txtACModel2.Text
            parAcSerial2.Value = txtACSerial2.Text
            parAcInstall2.Value = txtACInstall2.Text
            parAirClnr2.Value = txtAirClnr2.Text
            parAirClnrInstall2.Value = txtAirClnrInstall2.Text
            parHum2.Value = txtHUM2.Text
            parHumInstall2.Value = txtHumInstall2.Text
            parComments.Value = txtComments.Text

            cnConnection.Open()
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception

            End Try

            cnConnection.Close()
            dsMain.Tables("tblCustomers").Clear()
            daCustInfo.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblCustomers")
            daCustInfo.SelectCommand.Connection = cnConnection
            daCustInfo.Fill(dsMain, "tblCustomers")
            frwRoutines.bindData(dsMain, "tblCustomers", Me)
            navButtons.DataSet = dsMain
            navButtons.Table = "tblCustomers"
        End If
    End Sub

ScottyB Feb 16th, 2008 3:24 PM
Re: Insert command just doesn't work.
 
Hi,

I think what you will find is that you the cmd.ExecuteQuery statement is throwing an exception, as you have it in a try catch block, but because you are not doing anything with the caught exception the program continues to function. You either need to add some code to your catch block to act on the exception or remove the try catch statements to make the code fail and see the result.

Hope this helps.

SydneyMcConnell Feb 16th, 2008 5:32 PM
Re: Insert command just doesn't work.
 
I thought thats what it may be, but I've stepped through the code and even removed the try-catch, but it throws no error.

ScottyB Feb 16th, 2008 6:05 PM
Re: Insert command just doesn't work.
 
Hi,
Just had a closer look at the code and you are adding the parameters to the parameters collection of the command object before you add the value to each parameter

Maybe this block of code
parFirstName.Value = txtFirstName.Text
parLastName.Value = txtLastName.Text
parCompany.Value = txtCompany.Text
parAddress1.Value = txtAddress1.Text
parAddress2.Value = txtAddress2.Text
parCity.Value = txtCity.Text
parState.Value = txtState.Text
....
Should come before this..

cmd.Parameters.Add(parFirstName)
cmd.Parameters.Add(parLastName)
cmd.Parameters.Add(parCompany)
cmd.Parameters.Add(parAddress1)
cmd.Parameters.Add(parAddress2)
cmd.Parameters.Add(parCity)

Otherwise you are running your query with blank values. Consquently that is the reason no data is added to the database, because no data is being added to the command object.

Hope this helps.

SydneyMcConnell Feb 16th, 2008 7:33 PM
Re: Insert command just doesn't work.
 
Rahhh. >_<
Should have thought of that, thought I tried reversing that order. Guess I didn't, but I'll give it a try when I'm in work again and let you know. Thanks. =]


All times are GMT -5. The time now is 4:17 PM.

Powered by vBulletin® Version 3.7.0, Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Copyright ©2007 DaniWeb® LLC