Inserting a Long Integer into an MS Access Table with SQL 2

[Christineeve]

Hi
I hate to bother the community with such a trival question, but as much as I've researched since last week, for the life of me, I cannot see what is wrong with my syntax. Would you review my syntax and see if something leaps out at you and let me know what I might have missed?

My connection object works fine. My database is fine. When I pass this hardcoded query to my database it writes to the database. When I try to pass a value from the TextBox, the query stops with an error that say either that there is no value passed or there is a comma error.

I've researched this for several days. At this point, it would help to have expert eyes look at it and see if there are any suggestions.

This works:

Dim sSQLAddNameID = _
'"INSERT INTO tblEmpPersonal(pk_EmpInfoID) VALUES (10000001)"

This throws an error with every combination I've tried

    Dim sSQLAddNameID = _
    "INSERT INTO tblEmpPersonal(pk_EmpInfoID) VALUES (Trim(CInt(frmRibbonCtl.txtEmplyNo.Text))) "

I've researched this for days. Expert eyes with suggestions are sincerely appreciated.

Also, all the other fields write just fine, but they're text fields. Next I have to write a date. I'm guessing whatever I'm doing wrong here will also be a problem with the date. Again, if I hardcode they pass into the database fine. It's passing from the form that's become a problem.
Thanks Christine

 

[Sorwen]

It shouldn't make any difference, but I always use ctype for almost all type conversions. The few times I've had to do the same thing that is what I used.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Christineeve]

Thank you!

 

[Christineeve]

Would you mine showing me an example. Nothing I'm trying is working. It throws a compile error.

 

[gmmastros]

Ah....

You are creating a string, but you are not actually concatenating your values.

    Dim sSQLAddNameID = _
    "INSERT INTO tblEmpPersonal(pk_EmpInfoID) VALUES ([!]" & [/!]Trim(CInt(frmRibbonCtl.txtEmplyNo.Text))[!] & "[/!]) "

If my explanation doesn't make sense, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Christineeve]

George! Thank you that worked like a dream! I have tears of gratitude in my eyes because I've fought with this for days.

Would you also, if you have time, give me an example of how to pass a birthday in?

I had concated # on the date. Like I normally would in access. I get a failure but it might be because of the problem with the field you just helped me with.

If you don't have time, thanks anyway. The help I received today here has saved the day!

 

[gmmastros]

Dates can be a little tricky because they are not formatted consistently throughout the world. Some people use month/day/year, and some use day/month/year. Setting these issues aside....

Access uses the # symbol to delimit dates. So, when you build your query in code, you need to make sure they are there.

For example, if you try to execute a query that looks like this:

[tt]Update tblEmpPersonal Set Birthday=#3/23/1970# Where pk_EmpInfoID = 5[/tt]

Your challenge is to build a string that looks like this using code. So, something like this...

    Dim sSQLAddNameID = _
    "Update tblEmpPersonal Set Birthday=#" & txtBirthday.Text & "# Where pk_EmpInfoID = " & Trim(CInt(frmRibbonCtl.txtEmplyNo.Text))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Christineeve]

George,
The requirement is the US date format so we are ok there. But, you also helped me with my next quest which was working on the Updates.

I have all this working on hardcoded values and in the new data adapter stuff. I need to do it the old ADO.net SQL way and passing it in from the textboxes was not working right.

I appreciate your help so much.