Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

vb.net 2005 need help with saving to SQL database

Status
Not open for further replies.
DougP

DougP

MIS
Dec 13, 1999
5,985
0
36
US
I have this form and need help figuring out how to save the data from the form to a hosted WEB site SQL database.
Code: 
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        'If Not Page.IsPostBack Then
        Dim CrLf As String = Chr(13) & Chr(10)
        'save to database

        Try
            ' create connection to db
            Dim cnBKTest As Data.SqlClient.SqlConnection
            ' build connection string to db
            cnBKTest = New Data.SqlClient.SqlConnection("Server=000.000.000.000;Database=webdb1;User ID=user;Password=pass")
            ' create command for sp
            Dim cmdTest As New Data.SqlClient.SqlCommand("sp_InsertIntoR2D2Events", cnBKTest)
            ' specify command type as stored procedure
            cmdTest.CommandType = Data.CommandType.StoredProcedure

            ' set up prameters for stored Proc
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@EventDate", Data.SqlDbType.SmallDateTime))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@EventStartTime", Data.SqlDbType.SmallDateTime))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@EventEndTime", Data.SqlDbType.SmallDateTime))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@EventType", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@Company", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@ContactFName", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@ContactLName", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@ContactPhone", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationName", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationAddr1", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationAddr2", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationCity", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationState", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@LocationZip", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@Notes", Data.SqlDbType.NVarChar, 1000))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@Email", Data.SqlDbType.NVarChar, 100))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@SpecialInstructions", Data.SqlDbType.NVarChar, 100))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@DisclaimerCheckBox", Data.SqlDbType.Int))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@DisclaimerName", Data.SqlDbType.NVarChar, 50))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@Over18", Data.SqlDbType.Int))
            cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@UploadedFiles", Data.SqlDbType.Int))

            'put form values into parameters for Stored Procedure
            cmdTest.Parameters("@EventDate").Value = Me.txtDateChoosen.Text
            cmdTest.Parameters("@EventStartTime").Value = Me.ddlStartTime.Text
            cmdTest.Parameters("@EventEndTime").Value = Me.ddlEndTime.Text
            cmdTest.Parameters("@EventType").Value = Me.ddlEventType.Text
            cmdTest.Parameters("@Company").Value = Me.txtCompany.Text
            cmdTest.Parameters("@ContactFName").Value = Me.txtContactFName.Text
            cmdTest.Parameters("@ContactLName").Value = Me.txtContactLName.Text
            cmdTest.Parameters("@ContactPhone").Value = Me.txtContactPhone.Text
            cmdTest.Parameters("@LocationName").Value = Me.txtLocation.Text
            cmdTest.Parameters("@LocationAddr1").Value = Me.txtLocationAddr1.Text
            cmdTest.Parameters("@LocationAddr2").Value = Me.txtLocationAddr2.Text
            cmdTest.Parameters("@LocationCity").Value = Me.txtLocationCity.Text
            cmdTest.Parameters("@LocationState").Value = Me.txtLocationState.Text
            cmdTest.Parameters("@LocationZip").Value = Me.txtLocationZip.Text
            cmdTest.Parameters("@Email").Value = Me.txtEmail.Text
            cmdTest.Parameters("@Over18").Value = Me.chkIm18yearsold.Text
            cmdTest.Parameters("@SpecialInstructions").Value = Me.txtSpcialInstructions.Text
            'cmdTest.Parameters("@DisclaimerCheckBox").Value = Me.chk
            'cmdTest.Parameters("@DisclaimerName").Value =me.t
            'cmdTest.Parameters("@UploadedFiles".Value =

            cnBKTest.Open()
            cmdTest.ExecuteNonQuery()
            cnBKTest.Close()

            'Dim dt As New Data.DataTable()
            'Dim myConnection As New Data.SqlClient.SqlConnection("Server=000.000.000.000;Database=webdb1;User ID=user;Password=pass")
            'Dim strSQL As String = "Insert into R2D2Events (EventDate, EventStartTime, EventEndTime, EventType, Company, ContactFName, ContactLName, ContactPhone, LocationName, LocationAddr1, LocationAddr2, LocationCity, LocationState, LocationZip,  Email,  Over18, SpecialInstructions) 
            'VALUES (" & Me.txtDateChoosen.Text & ", " & Me.ddlStartTime.Text & ", " & Me.ddlEndTime.Text & ", " & Me.ddlEventType.Text & ", " & Me.txtCompany.Text & ", " & Me.txtContactFName.Text & ", " & Me.txtContactLName.Text & ", " & Me.txtContactPhone.Text & ", " & Me.txtLocation.Text & ", " & Me.txtLocationAddr1.Text & ", " & Me.txtLocationAddr2.Text & ", " & Me.txtLocationCity.Text & ", " & Me.txtLocationState.Text & ", " & Me.txtLocationZip.Text & ", " & Me.txtEmail.Text & ", " & Me.txtSpcialInstructions.Text & ", " & Me.chkIm18yearsold.Text & ")"
            'Dim strSQL As String = "sp_InsertIntoR2D2Events"
            'Dim myCommand As New Data.SqlClient.SqlCommand(strSQL, myConnection)
            'myCommand.ExecuteNonQuery()
        Catch ex As Exception
            lblErrorStatus.Text = ex.ToString()
        End Try

        'send email to them saying this is subject to approval

        Dim EmailSubject, EmailBody As String
        EmailSubject = "Thank you for submitting your request to us"
        EmailBody = "Please allow 36hrs for completion of your request for R2-D2 to show up..." & CrLf
        EmailBody = EmailBody & "   at the " & ddlEventType.Text & " located at " & txtLocation.Text & CrLf
        EmailBody = EmailBody & "" & CrLf
        EmailBody = EmailBody & "You will be notified by an email sent to " & txtEmail.Text & " if you're request has been approved or rescheduled." & CrLf
        EmailBody = EmailBody & "" & CrLf
        EmailBody = EmailBody & "Please contact us Immediately if there is a Mistake, Cancellation, or other issue."
        EmailBody = EmailBody & "" & CrLf
        EmailBody = EmailBody & "Thank you, xxxx" & CrLf
        EmailBody = EmailBody & "(999) 999-9999" & CrLf
        EmailBody = EmailBody & "Email: myemail@theserver.com"

        Try
            Dim message As New MailMessage("myemail@theserver.com", txtEmail.Text, EmailSubject, EmailBody)
            Dim emailClient As New SmtpClient("localhost")
            emailClient.Send(message)
            lblErrorStatus.Text = "Message Sent"
        Catch ex As Exception
            lblErrorStatus.Text = ex.ToString()
        End Try

        'End If
        'send email to me

    End Sub

I can use any method as shown a SP or raw code.

DougP
[r2d2] < I Built one
 
Sort by date Sort by votes
I don't have time to rewrite your code, but here's some sample code I have that I know works. I took out some of the parameters to shorten it up.
Hope this helps!
~Brett

Code: 
    Public Sub SP_NW7316_Insert_Services(ByVal ins_MNMmbrno As String, ByVal ins_MSVsrvno As String, ByVal ins_MSVeffec As String, ByRef ins_ErrorMsg As String, Optional ByVal ins_ConnString As String = "", Optional ByVal ins_ConnName As String = "")
        '*** SQL Stored Procedure(s): NW7316_Insert_Services ***
        Dim SP_Name As String
        Dim sqlAdapt As New Data.SqlClient.SqlDataAdapter

        '*** Initialize Database Connection ***
        If (ins_ConnString.Trim.Length > 0) Then
            '*** Connection String was Passed In ***
            strConnString = ins_ConnString
        Else
            '*** Build the Connection String ***
            strConnString = "Data Source=" & strDataSource & ";Initial Catalog=" & strInitCatalog & ";Integrated Security=SSPI"
        End If

        '*** Create Database Connection ***
        Dim sqlConn As New Data.SqlClient.SqlConnection(strConnString)

        '*** Initialize the Stored Procedure ***
        If (ins_ConnName.Trim.Length > 0) Then
            '*** Catalog & Database Schema String were Passed In ***
            SP_Name = ins_ConnName & ".NW7316_Insert_Services"
        Else
            '*** Build the Catalog & Database Schema String ***
            SP_Name = strInitCatalog & "." & strDBSchema & ".NW7316_Insert_Services"
        End If

        '*** Set Up the Stored Procedure ***
        Dim sqlCmd As New Data.SqlClient.SqlCommand(SP_Name, sqlConn)

        '*** Tell ADO.Net That it's a Stored Procedure ***
        sqlCmd.CommandType = Data.CommandType.StoredProcedure

        '*** Populate the Member Number ***
        Dim tmpMbrNo As Integer = CInt(Microsoft.VisualBasic.Val(ins_MNMmbrno.ToString.Trim))
        sqlCmd.Parameters.Add("@MSVmbrno", Data.SqlDbType.Int).Value = tmpMbrNo

        '*** Populate the Service Code ***
        If (ins_MSVsrvno.ToString.Trim.Length > 0) Then
            sqlCmd.Parameters.Add("@MSVsrvno", Data.SqlDbType.NVarChar).Value = ins_MSVsrvno.ToString.Trim
        Else
            sqlCmd.Parameters.Add("@MSVsrvno", Data.SqlDbType.NVarChar).Value = "00"
        End If

        '*** Populate the Effective Date ***
        Dim tmpDate As DateTime = CDate(ins_MSVeffec.ToString.Trim)
        If (ins_MSVeffec.ToString.Trim.Length > 0) Then
            sqlCmd.Parameters.Add("@MSVeffec", Data.SqlDbType.SmallDateTime).Value = tmpDate
        Else
            sqlCmd.Parameters.Add("@MSVeffec", Data.SqlDbType.SmallDateTime).Value = ""
        End If

        Try
            '*** Blank Out the Error Message ***
            ins_ErrorMsg = ""
            '*** Open the Connection ***
            sqlConn.Open()
            '*** Execute the Stored Procedure ***
            sqlCmd.ExecuteNonQuery()
            '*** Execute the SQL Command ***
            sqlAdapt.SelectCommand = sqlCmd
            '*** Close the Connection ***
            sqlConn.Close()
        Catch SQLex As Exception
            '*** Error Catching Done Here ***
            ins_ErrorMsg = SQLex.ToString.Trim
        End Try

        '*** Dispose of the Connection ***
        sqlAdapt.Dispose()
        sqlCmd.Dispose()
        sqlConn.Dispose()
    End Sub

--------------------------
Web/.net Programmer & DBA
Central PA
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
187
strongm
strongm
R_a_i_n_e_r_8
  • Locked
  • Question
Compile error: Types of intolerance (need help)
Replies
3
Views
189
strongm
strongm
tomminil4
  • Locked
  • Question
Help with VBA code
Replies
3
Views
114
strongm
strongm
2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
87
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
SORT msflexgrid column 1 with decimal number 1
Replies
4
Views
106
2009luca
2009luca

Part and Inventory Search

Sponsor

Back
Top