Error connecting to database

[Reham Ejaz]

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.

[wijitstyp]

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

[Reham Ejaz]

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

[campkev]

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.

[Reham Ejaz]

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.

[f1 fan]

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

[Reham Ejaz]

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

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.

[Reham Ejaz]

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.

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

[f1 fan]

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.

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.

[Reham Ejaz]

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.

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.

[campkev]

what do the insert and update statements look like?