Error connecting to database
 

I am having problem in connecting to database. The problem is with the following code.

dim objCmd as new OleDbDataAdapter()

objCmd.SelectCommand.Connection = dbconn

objCmd.SelectCommand.CommandText= sql 

I am having error on line2

the error is

System.NullReferenceException: Object variable or With block variable not set.

I think there is problem in my connection string but i cant figure out.

the connection string is

dim strConnection as String="Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("mydb.mdb")

Dim dbconn as New OleDbConnection(strConnection)

i have also tried

dim dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=c:/inetpub/wwwroot/day1/mydb.mdb")

but still its not working. I have also seen some tutorials but they didnt help me. Can anyone plz tell me what could be wrong. I want to insert values into database from textboxes.
But i am facing this connectivity error.

Re: Error connecting to database
 

Pls make sure abt "Connection String" for connect to MS Access database at http://www.connectionstrings.com

Re: Error connecting to database
 

Thanx. I visited the site Connection string is right. But i am still unable to insert or delete values from database.

Re: Error connecting to database
 

two things:
1) if you can read but can't update or delete, may have a problems with settings on the access database. Right-click on it. Go to security tab. make sure the internet guest account user has read and write privileges checked.
2) if you can't even read, try setting up a dsn connection. Let me know if you need help with that.

Error in updating database
 

There wasnt any problem with the settings. Now I have succeeded in updating simple database via OleDBDataAdapter and OleDbCommandBuilder objects but the same code is not working when I am using to update too many columns. Actually I cant figure out the error in my following code.

dim conn as new oledbconnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\wwwroot\website\visitor.mdb")

Dim cmd As OleDbDataAdapter

Dim ds As New DataSet()

Dim dr As DataRow

sub submit_clicked(Sender as object, e as Eventargs)



                dim i,j as integer

                   dim str as string

                'dim countrystr,  tourstr as string

                   dim param(7) as string

                   dim flag as boolean=true



                        

'Inserting values from text boxes into a string array



                   j=0

                     for i=0 to panel1.controls.count-1

                  if panel1.controls(i).GetType Is GetType(textbox) then

                           str=Ctype(panel1.controls(i),textbox).text

                        if str <> "" then

                                     param(j)=str

                        else

                                    flag=false

                            label1.text="forgot to enter  value for " & panel1.controls(i).id

                        end if

                               j=j+1

                   end if

                    next

                

                   if not flag then

                             exit sub

                   end if





                try

                         cmd = New OleDbDataAdapter(New OleDbCommand("select * from visitors",conn))

                        Dim comb As OleDbCommandBuilder = New OleDbCommandBuilder(cmd)

                        conn.Open()

                        cmd.fill(ds,"visitorinfo1")

                        dr = ds.Tables("visitors").NewRow()



                catch ex as exception

                        label1.text="Error in updating database"

                end try



'Entering values in datarow from textboxes

                

                dr("Name") = param(0)

                dr("Email") = param(1)



                if rdbgender.SelectedIndex = 0

                        dr("Gender") = "Male"

                else

                        dr("Gender") = "Female"

                end if



                dr("Age") = param(2)

                dr("Address") = param(3)

                dr("Phone No") = param(4)

                countrystr = ddlcountry.SelectedItem.Text

                dr("Country") = countrystr

                dr("Tour Type") = rdbtour.SelectedItem.Text

                tourstr = lbdetails.SelectedItem.Text

                dr("Tour Detail") = tourstr

                

                dr("Custom Tour") = param(5)

                dr("No of Travellers") = param(6)

                



                ds.Tables("visitors").Rows.Add(dr)

                

                cmd.Update(ds, "visitors")

                label1.text="database updated"



        end sub

Name, Age, ...... are the names of columns in database table in Access

ddlcountry and ddldetails are the id's of dropdownlist controls
rdbtour is id of the radiobutton control in html part of page.


Now with this code I am having problem on the line

cmd.Update(ds, "visitor")

Error is
Syntax error in INSERT INTO statement

If anyone can figureout the error in this code plz let me know. I will be thankful.

Re: Error connecting to database
 

you havent written an insert, update or delete query. Just a select query. your cmd is a "select" command it ONLY knows about getting data. You need a new command for each of the CRUD actions you intend to do (you should in theory write one for each but if you know 100% you will never need a delete then you can leave it out). So you cannot call Update on a command whos SQL is a SELECT query.

Create a new command but this time an Insert Command (with Inser INTO sql or the name of an insert stored proc or query in your database.

I would recommend you use a data adapter and fill all 4 commands in and use this to do everything you need.

If you need more explanation let me know

Re: Error connecting to database
 

The command builder object sees the changes in our data set and automatically makes the changes accordingly in the datastore. So with it we dont need insert statement.

Re: Error connecting to database
 

ok now i would appreciate any more help. I have tried it with insert statement as well but strill its not working.

Re: Error connecting to database
 

You misunderstood that. The data adapter sees the rows that have been changed (it actually iterates through each table and checks the row state) and depending on whether the row state is one of added, modified or deleted it calls the appropriate command to do the work.
If you take a step back to think about it... it is impossible for the command to know which value you want to go where. It knows nothing about your database (it only knows to pass some string to the database - the command does not execute it nor check it - you could have set the string as "send me ten million us dollars and a pizza" and it would only fail when you tried to execute it (the database would fail not the command)). So you have to tell it. Trust me, i have been doing this for a long time and spend a lot of time writing each of the stored procs for SQL (well i dont - i have written a generator, but the generator spends a lot of time writing them and i spend a lot of time telling the adapter each one).

You have to write it. Humor me and try it.

Re: Error connecting to database
 

But It did work. I mean the code I have given. The only problem now is that its working with some tables but not with all. I dont know what's the problem. I have made a table with personal user entries and its working fine. But when I make a new table with all the entries along with tour details. It gives an error on the update statement as I mentioned earlier. If it works with 1 table it should work with all. I have even tried using insert statement but it gives an error indicating syntax error in insert into statement the same error i get in update statement. Can u help me with that.