Writing data to a table

[rlegg369]

Hi, I'm new to database programming and I seem to be doing something wrong.
I have an application program where I use a datetimepicker to choose a date. I convert the selected date to a string, and then attempt to write it to one of the tables in my database. The table(weekEnding) has only two fields, the key field(WEID), and a string field(WeekEnding)

Here is the code I'm using:

Private Sub AcceptDateBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcceptDateBtn.Click
Dim SQLString As String = "Insert INTO WeekEnding(WeekEnding) VALUES(SelectedWeekEnding)"
Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Timesheet.mdf;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand()
Dim SelectedWeekEnding As String

cmd.Connection = conn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = SQLString

SelectedWeekEnding = DateTimePicker1.Text.ToString

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

WeekEndingGrpBox.Visible = False
End Sub


When it comes to the ExecuteNonQuery I get the following exception

The name "SelectedWeekEnding" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Can someone please explain what I'm doing wrong or how to get the string written into the table?

 

[Tom]

You would be better off adding a parameter to your command object and passing the value there. The variable you are using is not being seen while being part of the string you have created.

 

[rlegg369]

That helped a little, but new problem now...

I changed the code as follows:

Private Sub AcceptDateBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcceptDateBtn.Click
Dim SelectedWeekEnding As String
SelectedWeekEnding = DateTimePicker1.Text.ToString
Dim SQLString As String = String.Format("Insert INTO WeekEnding(WeekEnding) VALUES({0})", SelectedWeekEnding)
Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Timesheet.mdf;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand()

cmd.Connection = conn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = SQLString

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

WeekEndingGrpBox.Visible = False

This does not create an exception, but it doesn't quite work either.
the datetimepicker is set to return a short value. I choose 10/2/2009 but it does not show up in the table data.
I'm unsure about the key field. It is an integer called WEID, and I am asuming the value is populated as the next value when I add the string value to WeekEnding.
I have a list box on the form which is databound to WeekEndingBindingSource as the data source, the Display member is WeekEnding, the Display Value is WEID.

There are two records in the table, and when I start the program, the list box is populated with the two records correctly. When I run the above code it does not fail, but the date expected in the list box is not 10/2/2009, but instead shows displays a zero

Any Idea what I'm doing wrong?

 

[Tom]

Parameters are better then concatenating strings but at least you changed it to the string.fomat method. The only thing you need to do is put single quotes around the value. For example:

SQLString = String.Format("Insert INTO WeekEnding(WeekEnding) VALUES([COLOR="Red"][B]'[/B][/COLOR]{0}[COLOR="red"][B]'[/B][/COLOR])", SelectedWeekEnding)

 

[rlegg369]

Thanks Tom.

I really appreciate your help.

That appears to have worked for as long as I am in debug. When I end the session however, and show the table data, it is not there. Only the two records which I loaded into the table when I created it are shown. How is the new data actually supposed to be written to the table? I assumed this would be updating the actual table.

 

[Tom]

That insert statement will insert new records to the database. Are you sure your looking in the right database? Why are you using an attach database statement for your connection anyway?

Data Source=.\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=True

 

[rlegg369]

When I databound the list box, the data source configuration wizard created this connection string. I copied this connection string from what was created.

you had posted this:
Data Source=.\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=True

I used this substituting DATABASENAME with Timesheet.mdf so it read as follows:

Data Source=.\SQLEXPRESS;Initial Catalog=Timesheet.mdf;Integrated Security=True but got an exception when it tried to open the file. So I tried this one:
Data Source=.\SQLEXPRESS;Initial Catalog=|DataDirectory|\Timesheet.mdf;Integrated Security=True
But I get the same exception.

Here is the exception I get either way.
Cannot open database "|DataDirectory|\Timesheet.mdf" requested by the login. The login failed. Login failed for user 'LEGG-123\Ron'.

However with the attached database created by the wizard, the file opens seemingly without problems. The only problem seems to be that the data does not get written into the database.

 

[Tom]

Seems like you have multiple copies of the database floating around, your writing to one but viewing another; atleast thats my guess. Also the login failure you describe sounds more like a security rights problem, do you have all set to windows authentication? Its not like MS Access, you shouldnt need to tell it the exact folder that the mdf is located as long as you are pointing to the server location.

With the Express version, do you have the management studio? If so go into your database and right click on it and choose properties, then files and see where it is pointing too for your mdf and also your log file (.ldf).

 

[rlegg369]

Thanks for your help Tom

I don't know if it is the right way, but what I finally did was to go into the management studio and create the database there. Then in my application program I added the database I created in management studio. I dropped the previous database I had created in the application. When I performed the insert in the application, and refreshed the list box, the data was there. Then when the application was terminated, I checked the database, and the table I inserted into also had the data I added.

I'm quite sure I'll have more questions in the future, but I really apppreciate your help. It made me look at things a little differently.
FYI, here is the code as it looks now. Once again I took the connection string from the connection string created by the wizzard. It still uses the attached database, but it does work.

Private Sub AcceptBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcceptBtn.Click

Dim SelectedWeekEnding As String
SelectedWeekEnding = DateTimePicker1.Text.ToString
Dim SQLString As String = String.Format("Insert INTO WeekEnding(WeekEnding) VALUES('{0}')", SelectedWeekEnding)
Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\TimeSheet.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
Dim cmd As New SqlCommand()

cmd.Connection = conn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = SQLString

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

Me.WeekEndingTableAdapter.Fill(Me.TimeSheetDataSet1.WeekEnding)
WeekEndingComboBox.Refresh()

WeekEndingGrpBox.Visible = False
End Sub