SQL db connection help

[Stogie03]

hey guys, been reading through the tutorial by mendhak(sp?). Im trying to connect to a sql database.

in the class section of the form i have this code:

   Dim ds As New DataSet()
    Dim strSQL As String = "SELECT customer, custid, title, titleid, marketseg, frequency, reason, term, prepress, press, bind, ancillary, paper, prepressbm, pressbm, bindbm, ancillarybm, paperbm, Location FROM assesments"
    Dim da As New OleDbDataAdapter(strSQL, conn)
    Private conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= C:\Estimating\maindb.mdf"

In the form load i have this code:

da.Fill(ds)

And for updating the db i have this code in a button click event:

Dim dr As DataRow
        dr = ds.Tables(0).Rows(1)
        dr.BeginEdit()

        dr("paperBM") = PaperTotPrice
         .
         .
         .
        dr.EndEdit()

        da.Update(ds)
        ds.AcceptChanges()

The problem im getting is when the form loads(well it actually freezes before it loads) i get an error that says connection string not initialized. What error am i making?

[PilgrimPete]

Try moving the line that creates the data adapter (Dim da....) after the line that creates the connection string (Private conn...)

That should help.

[mendhak]

You'll notice that you're attempting to use 'conn' before it has been assigned a value.

[Stogie03]

Thanks i will try that out now.

[Stogie03]

I tried it but when i try and fill the table with da.Fill(ds) i get an unknown db format on "C:\Estimating\maindb.mdf"

Is this not a suitable method for .mdf's? This is a SQL database.

[Stogie03]

I realized the setup of the connection string is wrong for a sql database.

I changed the connection string to this:

Private conn As String = "provider=System.Data.SqlClient;" & "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MainDB.mdf;Integrated Security=True;User Instance=True"

but now when i get to da.fill(ds) i get this following error:

The 'System.Data.SqlClient' provider is not registered on the local machine.

What is wrong?

[brucevde]

System.Data.SqlClient is not an OLEDB provider it is a .NET Namespace. The OLEDB provider for SQL Server 2005 is named SQLNCLI (for SQL Sever 2000 it is sqloledb).

But since your database is SQL Server, use the .NET objects of the System.Data.SqlClient Namespace instead, ie use SQLConnection, SQLDataAdapter, SQLDataReader instead of the OLEDB counterparts.

Check connectionstrings.com for the right connection string to use for your situation.

[Stogie03]

Ok, im not getting any errors, but the row im adding isnt updating in the database. here is code:

Class:

 Dim ds As New DataSet()
    Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MainDB.mdf;Integrated Security=True;User Instance=True")
    Dim strSQL As New SqlDataAdapter("SELECT customer, custid, title, titleid, marketseg, frequency, reason, term, prepress, press, bind, ancillary, paper, prepressbm, pressbm, bindbm, ancillarybm, paperbm FROM assesments", conn)

Form Load:

adapter.Fill(ds)

Button click (method to insert row/update):

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim table As New DataTable 'Retrieve the data.
        adapter.FillSchema(table, SchemaType.Source)


        Dim dr As DataRow = table.NewRow

        'dr.BeginEdit()
        dr("customer") = CustomerName
        dr("custid") = CustomerNum
        dr("Title") = title
        dr("TitleID") = title
        dr("MarketSeg") = marketSeg
        dr("Frequency") = frequency
        dr("Reason") = reasonForAssess
        dr("term") = termofquote
        dr("prepress") = prepressTotal
        dr("press") = CoverTotal + BF1Total + BF2Total + BF3Total + BF4Total + coatingtotal
        dr("bind") = bindTotal
        dr("ancillary") = 1
        dr("paper") = PaperTotPrice
        dr("prepressBM") = prepressTotalBM
        dr("pressBM") = pressbm
        dr("bindBM") = bindTotalBM
        dr("ancillaryBM") = 1
        dr("paperBM") = PaperTotPrice
        'dr.EndEdit()

        table.Rows.Add(dr)
        table.AcceptChanges()
        adapter.Update(table)


    End Sub

Why isnt it staying in the database?

[brucevde]

The AcceptChanges method does not write data to the database. It is used to reset the RowState property of all new/modified/deleted rows to "unchanged".

One option for you is to use the DataAdapter.Update method to write to any changes to the database. Make sure you set the DataAdapter.InsertCommand property (and the UpdateCommand/DeleteCommand properties if neccessary) either by manually creating a SQLCommand or by using the SQLCommandBuilder.

        table.Rows.Add(dr)

        Dim cmd As SqlCommandBuilder = New SqlCommandBuilder(adapter)
        adapter.InsertCommand = cmd.GetInsertCommand

        adapter.Update(table)

You don't need to call the AcceptChanges method because the DataAdapter.Update method calls it automatically if the update was successfull.

[Stogie03]

Brucevde,

I removed the acceptchanges line and replaced it with:

Dim cmd As SqlCommandBuilder = New SqlCommandBuilder(adapter)
        adapter.InsertCommand = cmd.GetInsertCommand

Program runs error free but when i open the table for the database in visual studio the new record is still not in the table. Is there other code im missing or something not set up right with the SQL database?

[mendhak]

http://www.vbforums.com/showthread.php?t=466658

[Stogie03]

That is the tutorial im trying to follow mendhak but im confused on why its not updating the database. what am i missing in the code above?
i just tried adding:

adapter.UpdateCommand = New SqlCommand("UPDATE assesments SET customer=customername,.........WHERE id=2)
adapter.UpdateCommand.Connection = conn
        adapter.UpdateCommand.Parameters.Add("customername", SqlDbType.Text, 40, "customer")
        adapter.UpdateCommand.Parameters.Add("customernum", SqlDbType.Text, 40, "custid")
        adapter.UpdateCommand.Parameters.Add(2, SqlDbType.Int, 40, "id")

but still no update to the database

[mendhak]

What does your exact code look like? I'm assuming you don't have the .......... in the real code.

[Stogie03]

I just decided to erase the whole thing and do it over using a data control. So im trying that method instead.

[Stogie03]

I am still having trouble getting the new row to stay in my SQL database. Does it have to be configured a certain way to accept new data? Here is the code i am running to insert a new row:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim myCommand As SqlCommand
        Dim ra As Integer

        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MainDB.mdf;Integrated Security=True;User Instance=True")


        myConnection.Open()
        myCommand = New SqlCommand("Insert into Assesments VALUES ('" & CustomerName & "','" & CustomerNum & "','" & title & "','" & title & "','" & marketSeg & "','" & frequency & "','" & reasonForAssess & "','" & termofquote & "','" & prepressTotal & "','" & pressbm & "','" & bindTotal & "',  1,'" & PaperTotPrice & "','" & prepressTotalBM & "','" & pressbm & "','" & bindTotalBM & "', 1,'" & PaperTotPrice & "')", myConnection)
        myCommand.Connection = myConnection


        ra = myCommand.ExecuteNonQuery()

        myConnection.Close()
end sub