Database Connection Problem

[khan_naved]

:crushed: Hey guys, I m having problem with my connection to access database, i dnt knw where i m doing mistake, i m simply making application with two textboxes( User Id, Password) and buttons( Add, Update, Delete) through which i can modify database, i tried many code for the controls but it's not working, can somebody help me and send code for the above said application. no next or previous button. plz help guys.

 

[jmcilhinney]

If you were to show us what you did then we could tell you what you did wrong. There's not much point our posting code here because there are data access examples all over the web so if you weren't able to follow them then there's obviously a lack of understanding there that copying and pasting our code won't fix.

 

[khan_naved]

error : Format of the initialization string does not conform to specification starting at index 1.

Dim conn As OleDb.OleDbConnection
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\PROJECT.mdb;Persist Security Info=False"
Dim connString As String
Dim cmd As OleDb.OleDbCommand
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
connString = " INSERT INTO Login (LoginId, Password, Department) VALUES (trim(TextBox8.text), trim(TextBox9.text),trim(TextBox10.text))"
'connString = " Insert into Login (LoginId, Password, Department) VALUES" & ("(" + "'" + TextBox8.Text + "'" + "," + "'" + TextBox9.Text + "'" + "," + "'" + TextBox10.Text + "'" + ")")
conn = New OleDb.OleDbConnection(connString)
cmd = New OleDb.OleDbCommand(connString, conn)
da = New OleDb.OleDbDataAdapter(connString, strSQL)
cb = New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Login").NewRow()
dsNewRow.Item("LoginID") = TextBox8.Text
dsNewRow.Item("Password") = TextBox8.Text
dsNewRow.Item("Department") = TextBox10.Text
ds.Tables("Login").Rows.Add(dsNewRow)
da.Update(ds, "Login")
MsgBox("New Record added to the Database")


da.Fill(ds, "hm")

conn.Close()


*above lines are written in button click event to add data to database

 

[jmcilhinney]

This is an example of why you MUST post the relevant code. There's not too much wrong with your code so for us to provide you with all the code to do what you want would have been a waste of time. The issue is here:

Dim conn As OleDb.OleDbConnection
[COLOR="#008000"]Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\PROJECT.mdb;Persist Security Info=False"[/COLOR]
Dim connString As String
Dim cmd As OleDb.OleDbCommand
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
[COLOR="#FF0000"]connString = " INSERT INTO Login (LoginId, Password, Department) VALUES (trim(TextBox8.text), trim(TextBox9.text),trim(TextBox10.text))"[/COLOR]

You populate the 'connString' variable with a connection string, as is appropriate. You then replace that connection string with a SQL statement, which is obviously wrong. That SQL statement should be going somewhere else. You have a 'strSQL' variable being used elsewhere in the code. Presumably that is where your SQL statement should be going.

That said, there are some issues with your code besides that. You're creating a command and never using it and you're initialising a data adapter with an INSERT statement instead of a SELECT statement. You're also writing an INSERT statement and using a command builder. You might want to take a look at this, which shows you what objects to use and how for various common scenarios:

Retrieving and Saving Data in Databases

 

[khan_naved]

I used this in form load event.

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;Data Source=M:\PROJECTS.mdb")
conn.Open()
Sql = "SELECT * FROM Login"
da = New OleDb.OleDbDataAdapter(Sql, conn)
da.Fill(ds, "Login")

then button click event the above code given first.

 

[jmcilhinney]

You must make sure that you use the same DataSet and DataAdapter. There is an example of using the same objects to load and then save later in that link I provided. By the way, there's no need to explicitly opn the connection when calling Fill or Update. If you do open it explicitly though, you must close it explicitly too. Fill and Update will both open the connection automatically if it's not already open and then, if they opened it, they will close it automatically too. If it was already open though, they will leave it open.

 

[khan_naved]

after many try let's try another one...following coding is a recent one i tried but still getting error msg " Syntax error INSERT INTO statement "

Plz suggest correction or tell my mistake here.

' Button click event '

        Dim insert_constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\PROJECTS.mdb"
        ' Insert string 
        Dim insert_cmdstr As String = "select * from Login"
        ' insert command string      
        Dim insert_con As New OleDb.OleDbConnection(insert_constr)
        ' insert connection
        Dim insert_com As New OleDb.OleDbCommand(insert_cmdstr, insert_con)
        ' insert command (comand string, connection)
        insert_con.Open()
        ' connection open
        Dim insert_DR As OleDb.OleDbDataReader = insert_com.ExecuteReader
        ' insert Datareader 
        insert_con.Close()
        ' connection close
 
        Dim save_Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\PROJECTS.mdb"
        Dim save_Comstr As String
        Dim save_Con As OleDb.OleDbConnection
        Dim save_Com As OleDb.OleDbCommand
        Dim save_DA As OleDb.OleDbDataAdapter
        Dim save_DS As DataSet
        Dim save_DT As DataTable
 
        save_Comstr = "Insert into Login(LoginID,Password,Department)values" & _
        ("(" + "'" + TextBox8.Text + "'" + "," + "'" + TextBox9.Text + "'" + "," + "'" + TextBox10.Text + "'" + ")")
 
        save_Con = New OleDb.OleDbConnection(save_Constr)
        save_Com = New OleDb.OleDbCommand(save_Comstr, save_Con)
 
        save_DA = New OleDb.OleDbDataAdapter(save_Comstr, save_Constr)
        Dim save_builder As New OleDb.OleDbCommandBuilder(save_DA)
 
        save_DS = New DataSet()
 
        Try
            save_DA.Fill(save_DS, "Login")
            MsgBox("Record inserted successfully.", MsgBoxStyle.Information, "Register User")
          
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        save_Con.Close()

 

[jmcilhinney]

Your code is still a bit all over the place.

First up, you have two connections and two adapters. The whole point of a data adapter is that it works both ways, i.e. one adapter to retrieve and save the data.

Secondly, you are creating your own insert command and also using a command builder. The whole point of a command builder is that it builds the commands for you.

Finally, you are using string concatenation to get values into your SQL code, which is bad.

I suggest that you go back and check out those examples of mine again and note how I address those points. The examples that both retrieve and save data use one connection and one adapter. There is one example that creates all the SQL commands in code and the other uses a command builder, but never both. Finally, values are always inserted into SQL code using parameters. For more information on using parameters, follow the Blog link in my signature and check out my post on ADO.NET Parameters.

 

[cjard]

You would do yourself a massive favour by reading the DW4 link in my signature and following the Creating a Simple Data App tutorial, or watching some of the data videos in jmc's signature link HOW DO I?

Right now, youre making your life very much hard work and getting a lot of your data access code wrong. Heartbreaking as it may be to accept my advice of deleting everything you've written and starting over with adice from a proper Microsoft tutorial, you really really should.. They know how to tell you the correct way to code, because they wrote .NET, follow their advice

 

[khan_naved]

a button click event, Following code is for to add records to database, but having error as "Overflow".
what should i do ?
please suggest !!!

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim ent_Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\RCIncent.mdb"
        Dim ent_Comstr As String
        Dim ent_Con As OleDb.OleDbConnection
        Dim ent_Com As OleDb.OleDbCommand
        Dim ent_DA As OleDb.OleDbDataAdapter
        Dim ent_DS As DataSet
        'Dim ent_DT As DataTable

        ent_Comstr = "Insert into EntryLevel(CustName,CustMobile,CustOccupation,CustIncome,CustAge,CustStatus,CustAddress)values" & _
        ("(" + "'" + TextBox1.Text + "'" + "," + "'" + TextBox2.Text + "'" + "," + "'" + TextBox3.Text + "'" + "," + "'" + TextBox4.Text + "'" + "," + "'" + TextBox5.Text + "'" + "," + "'" + TextBox6.Text + "'" + "," + "'" + TextBox7.Text + "'" + ")")

        ent_Con = New OleDb.OleDbConnection(ent_Constr)
        ent_Com = New OleDb.OleDbCommand(ent_Comstr, ent_Con)

        ent_DA = New OleDb.OleDbDataAdapter(ent_Comstr, ent_Constr)
        Dim ent_builder As New OleDb.OleDbCommandBuilder(ent_DA)
        ent_DS = New DataSet()

        Try
            ent_DA.Fill(ent_DS, "EntryLevel")
            MsgBox("Record added successfully.", MsgBoxStyle.Information, "Entry Level")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ent_Con.Close()
    End Sub

 

[jmcilhinney]

You keep asking for help yet you keep ignoring the help provided. In post #4 I provided a link to some ADO.NET code examples. Have you actually followed that link? If you have then you haven't read it very well. In the first post of that thread there are five scenarios for which I provide examples. They are entitled:

1. Retrieving a single value.
2. Retrieving multiple records that will be read and discarded.
3. Retrieving multiple records for display that will not be updated.
4. Retrieving multiple records for display and editing, then saving the changes.
5. Saving changes directly to one or more records in the database.

Which one of those matches what you're doing? You're trying to insert a single record, right? That would be the fifth scenario, so why does your code look nothing like the example? Follow that link again and read it properly, then rewrite your code in line with the example that actually matches the scenario you're using in your app.

Also, I specifically said not to use string concatenation to build your SQL code but rather to use parameters, as I do in my examples. You are still not doing that either, which further suggests that you really haven't read that thread I linked to. If you really can't see the obvious difference between how I build my SQL code and how you're building yours, follow the Blog link in my signature and read my post on ADO.NET Parameters for a detailed explanation of why and how to use parameters.