adding data to access database

[pells]

I must be missing something real easy here, but I can not seem to insert data into access using .net. Here is my code.

 

Thanks,

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AJS Contact Management.mdb")

MyConnection.Open()

Dim strCommand As String = "Select * from Companies where company = '" & Replace(cmbcompany.Text, "'", "''") & "'and Contact = '" & Replace(cmbcontact.Text, "'", "''") & "'"

Dim mycommand As New OleDbCommand(strCommand, myconnection)

Dim myreader As OleDbDataReader = mycommand.ExecuteReader

 

If Not myreader.HasRows Then

myreader.Close()

mycommand.Dispose()

strCommand = "INSERT INTO Companies (company, address, city, state, zip, contact, sal, phone, ext, fax, email, customertype, discount VALUES ('" & cmbcompany.Text & "', '" & txtaddress.Text & "', '" & txtcity.Text & "', '" & cmbstate.Text & "', '" & txtzip.Text & "', '" & cmbcontact.Text & "', '" & cmbsalutation.Text & "', '" & txtphone.Text & "', '" & txtext.Text & "', '" & txtfax.Text & "', '" & txtemail.Text & "', '" & cmbcusttype.Text & "', '" & txtdiscount.Text & "'"

mycommand = New OleDbCommand(strCommand, myconnection)

mycommand.ExecuteNonQuery()

Else

MessageBox.Show("NAME ALREADY EXISTS!")

End If

 

myconnection.Close()

myreader.Close()

mycommand.Dispose()

[PlausiblyDamp]

What errors do you get? what does the contents of strCommand look like?

[techmanbd]

I see one problem

 

you insert statement is missing a parenthesis between the words discount) VALUES

[pells]

I get the following error on the executenonquery command.

 

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

[georgepatotk]

"INSERT INTO Companies (company, address, city, state, zip, contact, sal, phone, ext, fax, email, customertype, discount) VALUES ('" & cmbcompany.Text & "', '" & txtaddress.Text & "', '" & txtcity.Text & "', '" & cmbstate.Text & "', '" & txtzip.Text & "', '" & cmbcontact.Text & "', '" & cmbsalutation.Text & "', '" & txtphone.Text & "', '" & txtext.Text & "', '" & txtfax.Text & "', '" & txtemail.Text & "', '" & cmbcusttype.Text & "', '" & txtdiscount.Text & "')"

 

paste this in.. should work

[pells]

Still get the same error. It looks like the sql is fine, it just does not like the executenonquery statement.

 

Could it be a setting in access, or maybe I am missing a reference (I know this was big with VB6, but I am just learning the .net stuff).

 

I have a delete query that has the same statement, and it works fine. I just can not add anything new to the db.

[pells]

I tried creating a new table with just one field in it. I still get the same error. I tried using the wizzard with a new database, same error. Is there a security setting or soemthing I am missing?

[PlausiblyDamp]

Is there no other information displayed other than the exception type?

[pells]

Nope, just the error box and the error I listed above. If I break it goes right to that line.

 

If I mouse over that line in break mode it says

 

Public overridable nonoverrideable Function Exectuenonquerry () as integer.

 

I also notice that the insert statement is in red in the auto's window.

 

Thanks for any ideas, I am really stuck.

[georgepatotk]

I have an idea,

Since you are using access, I think it might be the permission toward to databases file problem.

[Keroleen]

Im running a simular process here. And I have a question.

I have no error message. The only problem, is that when i 'insert' into my Access database, all the fields are NULL, it doesnt 'insert'. Ive already added some error checking, and it says it inserts but not

 

Try
           Dim sqlStr As String = "INSERT INTO tblTimesheet (jobNumber, jobSubCode, jobProjectName, jobDescription, weekNumber) VALUES (' " & txt_number.Text & "', ' " & txt_subcode.Text & "', ' " & txt_projectname.Text & "', ' " & txt_description.Text & " ', ' " & txt_weeknumber.Text & "')"
           MsgBox(sqlStr) ** it display the correct statement but in the database it is blank
           Dim Command5 As New OleDbCommand(sqlStr, connection1)
       Catch ex As Exception
           MsgBox("Error")
       End Try

 

can someone help?

[georgepatotk]

Try this and see whether it works,

 

 

Try
           Dim sqlStr As String = "INSERT INTO tblTimesheet (jobNumber, jobSubCode, jobProjectName, jobDescription, weekNumber) VALUES (' " & txt_number.Text & "', ' " & txt_subcode.Text & "', ' " & txt_projectname.Text & "', ' " & txt_description.Text & " ', ' " & txt_weeknumber.Text & "')"
           MsgBox(sqlStr) ** it display the correct statement but in the database it is blank
           Dim Command5 As New OleDbCommand(sqlStr, connection1)
           Command5.ExecuteNonQuery()
Catch ex As Exception
           MsgBox("Error")
End Try

[Joe Mamma]

TRY PARAMETERIZING YOUR QUERY!!!

 

PLEASE!

EVERYBODY!!!

 

USE PARAMETERS, AND DO NOT, I REPEAT, DO NOT 'BUILD' SQL STRINGS!!!

 

IT IS POOR PRACTICE. .

WHY?

1. EXTREMELY COSTLY ON RESOURCES

Query must be prepared everytime the sql changes!!! can cause a batch update process to take 3x as long to execute

2. LEADS TO HARD TO FIND ERRORS!

what happens if there is a single quote in your text?!?!?

3. BUILT SQL IS NOT PORTABLE!

what? are you going to format dates/currency for every DB platform? 'We only use SQL server here' you might be saying. . . Well, thats until some new joe hot shot who controls the purse strings comes on board and has a buddy over at oracle that buys him beers and starts insisting you switch!

 

BE PREPARED!

 

BUILDING SQL STRINGS CAN BE THE DIFFERENCE BETWEEN GETTING A JOB AND NOT GETTING A JOB!!

INSIST THAT THOSE YOU WORK WITH DO THE SAME!

[Keroleen]

????

 

paramatize query? what do u mean by that?

Sorry I am completely NOOB.

Do u mind to explain?

[Joe Mamma]

????

 

paramatize query? what do u mean by that?

Sorry I am completely NOOB.

Do u mind to explain?

 

set your sql string = to something like

 

cmd.CommandText = "SELECT * FROM MYTABLE WHERE NAME = ? and DOB > ?"

then add parameters for each of your '?' like this:

cmd.Parameters.Add(new OleDbParameter("NAME", OleDBType.VarChar);
cmd.Parameters.Add(new OleDbParameter("DOB", OleDBType.DateTime);

prepare your command. This compiles your command for faster execution. Command Preparation is implicit and does not really need to be called as it will be called internally the first time the command is executed. In some rare cases explicitly calling it can speed performance.

cmd.Prepare

 

your cmd is ready to be used by just setting the parameter values:

 

 cmd.Parameters["NAME"] = "Miller"; 
cmd.Parameters["DOB"] = DateTime.Parse("12/2/1959");
cmd.ExecuteReader();

 

Now why is this better???

1. What if the name has a single quote in it or some other character the DB flavor is sensitive to?

 

2. The DateTime formatting is transparent to the command/db. the programmer shouldnt care how dates are formatted by the DB. dates are dates. . . strings are strings. . . let the dba format them how ever he wants on display.

 

3 since the command text never changes, the command is only prepared on the first initialization.