Access 2k insert

[ukjock]

Hello me again! lol

 

I need some help, would you epect anything different :rolleyes:

 

I am trying to insert some data in to a Access 2000 file with the following code:

 

       Dim line_string As String
       Dim sex_string As String
       Dim tel_number_string As String
       Dim callers_name_String As String
       Dim callers_location_string As String
       Dim topic1_string As String
       Dim topic2_string As String
       Dim graphic1_string As String
       Dim time_called_string As String

       line_string = ComboBox1.Text
       sex_string = ComboBox2.Text
       tel_number_string = TextBox1.Text
       callers_name_String = TextBox2.Text
       callers_location_string = TextBox3.Text
       topic1_string = TextBox4.Text
       topic2_string = TextBox5.Text
       graphic1_string = ComboBox3.Text
       time_called_string = Date.Now.ToString("T")

       Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database)


       MyConnection.Open()

       Dim MyCommand As New OleDbCommand("INSERT INTO Callers_View (ID, Line_Number, Sex, Tel_Number, Callers_Name, Callers_Location, Topic1, Topic2, Graphic1, Time_Called) VALUES ( '2' , 'line_string' , 'sex_string' , 'tel_number_string' , 'callers_name_String' , 'callers_location_string' , 'topic1_string' , 'topic2_string' , 'graphic1_string' , 'time_called_string')", MyConnection)

       MyConnection.Close()

       MyCommand.Dispose()

 

database is already declared as a string for the form.

 

I check the properties of the access file and it has not been accessed nor is anything being stored in it.

 

What am i doing wrong?

 

ID is set to be Autonumber and a PrimaryKey. I have attached a copy of the database to let you see what I mean.

 

Thanks for your help again guys and gals. It is much appreciated.

 

Chris

psDOTnet.zip

[Diesel]

You have forgotten a key line....

 

You forget to run the insert...

 

MyCommand.ExecuteNonQuery()

[ukjock]

Thanks for the reply but when I insert that line I seem to get the following error:

 

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

[Diesel]

Well...

 

At least it runs :)

 

Put a handler around the connection and the INSERT so you can see what the error is...

 

Try
 MyConnection.Open()
Catch ex as System.Data.Oledb.OledbException
 MyConnection.Close()
 Messagebox.show(ex.message)
End Try

       Dim MyCommand As New OleDbCommand("INSERT INTO Callers_View (ID, Line_Number, Sex, Tel_Number, Callers_Name, Callers_Location, Topic1, Topic2, Graphic1, Time_Called) VALUES ( '2' , 'line_string' , 'sex_string' , 'tel_number_string' , 'callers_name_String' , 'callers_location_string' , 'topic1_string' , 'topic2_string' , 'graphic1_string' , 'time_called_string')", MyConnection)

Try
     MyCommand.ExecuteNonQuery()
Catch ex as System.Data.oledb.oledbException
    Messagebox.show(ex.message)
   MyConnection.close()
End Try
       
MyConnection.Close()

       MyCommand.Dispose()

[Diesel]

And then...

 

And Whats in that database variable anyway?

[techmanbd]

There could be something wrong with your INSERT statement. One thing I noticed is that you have an ID column. Is that set up as NUMERIC or INTEGER? if it is you don't put the single quotes around the variable you are adding. Only characters and like VARCHAR you need to use the single quotes.

[joe_pool_is]

my 2 cents

 

I'm a newbie, too. But here's an idea:

 

Part of your SQL statement reads:

" ... VALUES ( '2' , 'line_string' , 'sex_string' , 'tel_number_string' , 'callers_name_String' , 'callers_location_string' , 'topic1_string' , 'topic2_string' , 'graphic1_string' , 'time_called_string')"

 

My question is: Are you trying to insert the value 'line_string' into your database, or the variable held by it (i.e. is line_string = "One happy day")? If line_string is a string variable, you would need to write your SQL statement kinda like this:

VALUES ( '2' , '" & line_string & "' , '" & sex_string & "' , ...

 

Catch my drift? This is VB.NET version, though. If you are using C#, it is probably a little different.

 

Good luck!

[techmanbd]

actually I see one of the problems. You said the ID is autonumber. So it is a number.

 

Values('2', etc) needs to be VALUES( 2, ets.). No single quotes around a number

[ost]

If "ID" is autonumber you shouldn't include it in your insert statement at all.

[ukjock]

Thanks for your help guys, it is much appreciated.

 

I have read through all the postings and I now have it sorted.

 

Thanks again

 

Chris