Results 1 to 15 of 15

Thread: SQL insert for access database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2006
    Posts
    29

    SQL insert for access database

    Very simple question..

    What is the command to just execute the insert statement on the database?

    command.?

  2. #2
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    if you mean the sql syntax here it is


    insert into tablename (column1,column2) values (column1value,column2value)

    if you mean other thing please clarify

    bst rgds

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2006
    Posts
    29

    Re: SQL insert for access database

    No sir. I am asking for the vb.net commands to do this. I have the query all built just wanting to know how to access the database without returning any values, or a boolean true false..

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL insert for access database

    Look at the command object and execute areas.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    the logic goes as following :

    1 - Create a connection to the database
    2- Create a command object and links it to the connection you created in step1
    3 - set the commandtext property of the command created in step 2 to your sql statement
    4- open the connection
    5 - execute the command bu using the executenonquery method of the command.
    6 - close the connection (very important)

    ' here is an example

    VB Code:
    1. dim cn as new sqlconnection("connectionstring goes here")
    2. dim cmd as new sqlcommand("sql insert goes here",cn)
    3. try
    4. cn.open
    5. cmd.executenonquery
    6. catch ex as exception
    7. msgbox(Ex.tostring)
    8. finally
    9. if cn.state = 1 then cn.close
    10. end try


    rgds

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2006
    Posts
    29

    Re: SQL insert for access database

    [Highlight=VB]
    Public dbcCSC As OleDbConnection
    Public Sub createCon()
    dbcCSC = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Randy\My Documents\Visual Studio Projects\WindowsApplication1\db1.mdb")
    dbcCSC.Open()
    End Sub

    Public Sub closeCon()
    dbcCSC.Close()
    dbcCSC = Nothing
    End Sub

    Public Function Insert(ByVal query As String)
    createCon()
    MsgBox(query)
    Dim command As New OleDbCommand(query, dbcCSC)
    command.ExecuteNonQuery() 'gets to here and stops
    closeCon()
    End Function

    query = "Insert into Login values (" & txtUserName.Text & "," & txtPassword.Text & ",0)"
    [/vbcod]

    These are in a module all connection based code and the query is being passed in from a form. When this starts to gexecute on the command.ExecuteNonQuery() it just sits there.

    Any ideas?

  7. #7
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    i think you have a problem with your query
    "Insert into Login values (" & txtUserName.Text & "," & txtPassword.Text & ",0)"
    string fields should be wrapped between ' ', so your query should be like :



    "Insert into Login values ('" & txtUserName.Text & "','" & txtPassword.Text & "',0)"

    Please note that i have surronded the text fields with ' and '

  8. #8
    Frenzied Member conipto's Avatar
    Join Date
    Jun 2005
    Location
    Chicago
    Posts
    1,175

    Re: SQL insert for access database

    Quote Originally Posted by maged
    i think you have a problem with your query


    string fields should be wrapped between ' ', so your query should be like :



    "Insert into Login values ('" & txtUserName.Text & "','" & txtPassword.Text & "',0)"

    Please note that i have surronded the text fields with ' and '
    Aren't the ' optional in the JET 4.0 Provider?

    Bill
    Hate Adobe Acrobat? My Codebank Sumbissions - Easy CodeDom Expression evaluator: (VB / C# ) -- C# Scrolling Text Display

    I Like to code when drunk. Don't say you weren't warned.

  9. #9
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL insert for access database

    In SQL all strings need to wrapped in Single Qoutes.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  10. #10
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    Aren't the ' optional in the JET 4.0 Provider?
    Who Said SO ?????????

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Feb 2006
    Posts
    29

    Re: SQL insert for access database

    Quote Originally Posted by maged
    i think you have a problem with your query


    string fields should be wrapped between ' ', so your query should be like :



    "Insert into Login values ('" & txtUserName.Text & "','" & txtPassword.Text & "',0)"

    Please note that i have surronded the text fields with ' and '
    This worked!!!!!!!

    Thank you very much i have been wrapping my head around this for a couple of hours...


    Thank you again!
    Randy

  12. #12
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL insert for access database

    Every database (RDMS) I have every worked with (Oracle,SQL Server,MySQL,SysBase,Ingress and others) all require text values being stored in text (char) fields be enclosed in single quotes. Although using parameters in ADO might get around that.
    Last edited by GaryMazzone; Apr 12th, 2006 at 09:56 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  13. #13
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    glad i could help randy,

    Although using parameters in ADO might get around that.
    only to your eyes, the command replaces paramters with values and adjust the suitable syntax before executing it over the datatabase.

  14. #14
    Frenzied Member conipto's Avatar
    Join Date
    Jun 2005
    Location
    Chicago
    Posts
    1,175

    Re: SQL insert for access database

    Quote Originally Posted by maged
    Who Said SO ?????????
    My fault, I was thinking it was optional, but i was thinking of the fact that it's interchangable with "

    Bill
    Hate Adobe Acrobat? My Codebank Sumbissions - Easy CodeDom Expression evaluator: (VB / C# ) -- C# Scrolling Text Display

    I Like to code when drunk. Don't say you weren't warned.

  15. #15
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL insert for access database

    Who Said SO ?????????
    theoritcally it cant be possible even in jet verion 99 because this single quotations identifies the start and the end of the string for the sql syntax parser. Without it the sql parser will think the rest of the sql statemnt as a the rest of the string.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width