code not saving to SQL database, I'm missing something

[DougP]

this is Visual Studio 2005, asp.NET 2.0

I'm missing something hopefully someone can tell me what it is. When I press the submit button I want to save info. to the db. When I press the submit button the code following this works fine to send a email. but this db part does nothing, no error either. I think I need more statements to execute it or something?
The passowrd and server, etc have been changed to protect the innocent.

       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
            Dim dt As New Data.DataTable()
            Dim myConnection As New Data.SqlClient.SqlConnection("Server=000.000.000.000;Database=myDb;User ID=user;Password=password")
            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 myCommand As New Data.SqlClient.SqlCommand(strSQL, myConnection)
        Catch ex As Exception
            lblErrorStatus.Text = ex.ToString()
        End Try

DougP
< I Built one

http://www.robots2000.com

 

[jbenson001]

You need to do:

myCommand.ExecuteNonQuery

But I also strongly suggest using a stored procedure with parameters for maintainability and security reasons.

 

[DougP]

still nothing, it says execute non query, but are'nt I executing a query?

        Try
            Dim dt As New Data.DataTable()
            Dim myConnection As New Data.SqlClient.SqlConnection("Server=000.000.000.000;Database=mydb;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 myCommand As New Data.SqlClient.SqlCommand(strSQL, myConnection)
            [COLOR=red]myCommand.ExecuteNonQuery()[/color]
        Catch ex As Exception
            lblErrorStatus.Text = ex.ToString()
        End Try

DougP
< I Built one

http://www.robots2000.com

 

[jmeckley]

put the connection string in the web.config file
use a parameterized query.

this will
1. make it much easier to maintain.
2. allow your sql to execute properly.

the reason your query is (most likely) not executing is because your sql statement is malformed. if you use your injected sql statement above the end result is

Insert into R2D2Events 
(
 EventDate, 
 EventStartTime,
 EventEndTime,
 EventType,
 Company,
 ContactFName,
 ContactLName,
 ContactPhone,
 LocationName,
 LocationAddr1,
 LocationAddr2,
 LocationCity,
 LocationState,
 LocationZip,
 Email,
 Over18,
 SpecialInstructions
) VALUES (
 2009-1-1,
 11:00:00,
 12:00:00, 
 event type,
 acme inc.,
 john,
 doe,
 555-555-5555,
 location,
 address line 1,
 address line 2,
 city,
 PA,
 13245,
 jdoe@email.com,
 special instructions,
 1)

all those values (except the bit for over 18) should be surrounded by single quotes. a parameterized query will do this for you automatically. it will also prevent sql injection attacks. which in this example would be very easy to execute.

both .net and sql treat dates as date and time within one object. I would combine these fields in your database as well. instead of start date, start time, end date, end time simply have startdatetime and enddatetime. calculations can be done using either sql or .net datetime functions.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[DougP]

Ok I took you're advice and am making an SP.
how do I call the SP using some of the above code, Maybe?

Create PROCEDURE sp_InsertIntoR2D2Events 
	-- Add the parameters for the stored procedure here
	 @EventDate as smalldatetime,
	 @EventStartTime as smalldatetime,
	 @EventEndTime as smalldatetime,
	 @EventType as Nvarchar(50),
	 @Company as Nvarchar(50),
	 @ContactFName as Nvarchar(50),
	 @ContactLName as Nvarchar(50),
	 @ContactPhone as Nvarchar(50),
	 @LocationName as Nvarchar(50),
	 @LocationAddr1 as Nvarchar(50),
	 @LocationAddr2 as Nvarchar(50),
	 @LocationCity as Nvarchar(50),
	 @LocationState as Nvarchar(50),
	 @LocationZip as Nvarchar(50),
	 @Notes as Nvarchar(1000),
	 @Email  as Nvarchar(100),
	 @SpecialInstructions  as Nvarchar(100),
	 @DisclaimerCheckBox as Int,
	 @DisclaimerName  as Nvarchar(50),
	 @Over18 as Int,
	 @UploadedFiles as smalldatetime


AS
BEGIN

    -- Insert statements for procedure here
	Insert into  R2D2Events (EventDate, EventStartTime, EventEndTime, EventType, Company, 
							ContactFName, ContactLName, ContactPhone, LocationName, 
							LocationAddr1, LocationAddr2, LocationCity, LocationState, 
							LocationZip,  Email,  Over18, SpecialInstructions,DisclaimerCheckBox,
							DisclaimerName,UploadedFiles)
	VALUES (@EventDate, @EventStartTime,@EventEndTime,@EventType,@Company,
			@ContactFName,@ContactLName,@ContactPhone,@LocationName,
			@LocationAddr1,@LocationAddr2,@LocationCity,@LocationState,
			@LocationZip,@Email,@Over18, @SpecialInstructions,@DisclaimerCheckBox,
			@DisclaimerName,@UploadedFiles)
END
GO

DougP
< I Built one

http://www.robots2000.com

 

[DougP]

jmeckley, would'nt the Catch statement fire an error if that was the case? I do see you are correct though, as nothing surrounds the Date #9-1-09# or other strings as you mentioned.



DougP
< I Built one

http://www.robots2000.com

 

[jmeckley]

it should. I cannot see any reason why it would not.

Ok I took you're advice and am making an SP.
how do I call the SP using some of the above code, Maybe?

the web has so much information

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[DougP]

you mentioned
"Ok I took you're advice and am making an SP."

Our posts must have crossed on the Stored Procedure thing. I posted it and then yours sliped in right above it. that was in regards to jbenson001 post whic he stated
Quote:
"But I also strongly suggest using a stored procedure..."

So I started re-wrting the code to make that happen and its taken a lot of time since I have so many fields to save

This whole thread/post is out of whack now.
Anyway if I need more help I'll start a new quesiton altogether


DougP
< I Built one

http://www.robots2000.com

 

[jmeckley]

i'll throw in another option. an ORM framework, instead of stored procs. Nhibernate, ActiveRecord and LLBL Gen Pro ($) are the biggest names for ORM.

teams using procs over an ORM are usually using code generation tool(s) to produce a bulk of the CRUD operations.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jbenson001]

teams using procs over an ORM are usually using code generation tool(s) to produce a bulk of the CRUD operations.

Where did you get that statistic from?

 

[jmeckley]

that's not statistical
it's what I've gathered from listening/reading to those who use procs.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[DougP]

The bottom line to it not working is the Date is not formatted correctly. I'm trying to put the date from a text box which has this "Thursday September 23 2009" into a Date field and it wants something like 9/23/2009.

Some eluded to that above
so if I have a text box like so
Me.txtDateChoosen.Text which has the value
Thursday September 23 2009
in it
how do I format it to be
9/23/2009




DougP
< I Built one

http://www.robots2000.com

 

[jbenson001]

There is no easy conversion, you will have to code it yourself. How does the textbox get populated. It you select a data, from a pop-up for example, change the format the is returned to the textbox.

 

[jmeckley]

datetime is an object. this object is stored in .net in the datetime object. in sql its the datetime datatype. how this value is displayed to the user is only a presentation concern which should be applied at the presentation level. example

ATextBox.Text = DateTime.Now.ToString("dddd, MMMM dd, yyyy");

or

ATextBox.Text = string.Format("{0:dddd, MMMM dd, yyyy}", DateTime.Now);

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190