How to add a new record into database......

  • Thread starter Thread starter KKuser
  • Start date Start date
K

KKuser

Hello......

I know this might be a kind of "FAQ", but somehow I really can't find any
clue...(including MSDN)...

My code is as following...

Dim drow As DataRow
Dim ada As New SqlDataAdapter()
Dim ads = New DataSet()
Dim astrConn As String = "data source=(local);persist security
info=False;user id=" + UID + ";password=" + Pwd
Dim aconn = New SqlConnection(astrConn)
aconn.Open()

drow = ads.Tables("i_cust").NewRow
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
the compiler stops here and says:
"System.NullReference.Exception" occurred
Object variable or With block variable not set

Could anyone tell me what's wrong and how to correct it?

Thanks!!
 
Hi KKuser,

I assume your code is complete and than the line is missing (see inline)
Dim drow As DataRow
Dim ada As New SqlDataAdapter()
Dim ads = New DataSet()
Dim astrConn As String = "data source=(local);persist security
info=False;user id=" + UID + ";password=" + Pwd
Dim aconn = New SqlConnection(astrConn)
aconn.Open()
Click to expand...

ada.fill(ads)

'the fill opens and close a connection automaticly (when the connection is
not already open), when you use one table it is more efficient to use that,
when you have to fill more tables in a dataset one after each other it is
more efficient to use the open and close.
drow = ads.Tables("i_cust").NewRow
Click to expand...

I hope this helps?

Cor
 
Hi Cor:

Thank you for your kind reply!! I have modified my code as following:

Dim drow As DataRow
Dim ada As New SqlDataAdapter(cmd)
Dim ads = New DataSet()
Dim astrConn As String = "data source=(local);persist security
info=False;user id=" + UID + ";password=" + Pwd
Dim aconn = New SqlConnection(astrConn)
cmd = New SqlCommand("Select * from dbo.i_cust", aconn)
cmd.CommandType = CommandType.Text

aconn.Open()

cmd.ExecuteNonQuery()
ada.Fill(ads)
drow = ads.Tables("dbo.i_cust").NewRow
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The compiler stops here again, and the error message is totally the same. By
the way, the "UID" and "Pwd" in the connection string are global variables
so there won't be any problem connecting to the database (I have checked
this).

Thanks again!!
 
Hi K,

You can put the fill part in a try and catch block (that is normal for this
kind of operaions), than you see if there is a connection error or whatever
and what it is.

I show it to you. (What is that cmdexecute non querry that is to get a value
from your database directly, however it has probably nothing to do with your
error)
aconn.Open()
try
ada.Fill(ads)
Click to expand...
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Conn.Close()
End Try
 
Hi Cor:

Thank you for your reply !!

In fact I did. I have put "Try" before
cmd.ExecuteNonQuery()
I just removed it when posting message on the mail.
There is no error metioned about
ada.Fill(ads)
, as I have said the compiler still stops at the same place......@@

The code is like this:

aconn.Open()

Try
cmd.ExecuteNonQuery()
ada.Fill(ads)
drow = ads.Tables("dbo.i_cust").NewRow
Catch ex as Exception
msgbox(ex.ToString)
End Try

**There seems to be no error of "SqlException"
 
Hi KKuser,

I forgot an alterenative often made error, however that is easy so overcome,
the table name is case sensetive, so there is maybe no table with that name.
But when you use the zero index it is with one table always the right one.
(I added that in the corrections bellow)

However why is that cmd.ExecuteNonQuery, do I not know something, it is in
my opinion completly for nothing, only consuming time.

And you should choise or you delete that open or you add that finally
statement I did show you with the close. This has nothing to do with the
error, however is bad use of the connections from the SQL server.

I hope this helps?

Cor

See bellow.
 
Hi Cor:

I think the problem is almostly resolved. I think the key point should be
the code "Table("XXXX")" and "Table(0)".
And I also removed the code "cmd.ExecuteNonQuery"......I didn't have any
problem running the app, as you said...
Since I didn't give it enough information for those "non-null" fields, the
app is not completed yet...but I don't think it would be a problem.

Thank you for your reply, and your opinions are appreciated very much (and
of great help too). : )
 
Back
Top