plz vb

Question

Farhan_B 0 Junior Poster in Training

12 Years Ago

im working on this piece of code and for sum reason i cannot understand why i keep getting this message can you please advise me

thanks in advance

'sql statements
        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        cmd.ExecuteNonQuery() ' execute oledb commands non query only
        acscmd.Dispose()
        MsgBox("Saved")

the text in red is were the error shows

vb.net visual-basic

3 Contributors
26 Replies
209 Views
10 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by kingsonprisonic

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

Please post the error message ...

hericles 289 Master Poster

12 Years Ago

Maybe you have that cmd object declared elsewhere but from what you posted the name of the command object is wrong.

Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
 acscmd.CommandText = strsql 'sets the sql string
 acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
 cmd.ExecuteNonQuery() ' execute oledb commands non query only

You declare and set parameters for acscmd but then call the executeNonQuery on cmd.

kingsonprisonic commented: +1 for eagle eye.... +5

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

Yes @hericles is right. I didn't notice that LOL....

+1 from me....

hericles 289 Master Poster

12 Years Ago

In your post above where you said you still got the error you had renamed the command object to asccmd instead of acscmd.

Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only
        acscmd.Dispose()

Copy that code and use it, you shouldn't get an error.

ARG!! beaten to it ;)

Edited 12 Years Ago by hericles because: n/a

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

Please notice carefully..

Sub connect()
        acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=..mdb
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
    End Sub

in your code

acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=..mdb

It is not a complete connection string for your database...

First use correct connection string...

Goto : http://www.connectionstrings.com/

Try the correct Connection String Format...

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Farhan_B 0 Junior Poster in Training · Answer 1 · 2012-02-29T05:45:54+00:00

its says

nullreferenceexception was unhandled.
object reference not set to an instance of an object

Farhan_B 0 Junior Poster in Training · Answer 2 · 2012-02-29T05:51:29+00:00

even when i change it to

Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
 acscmd.CommandText = strsql 'sets the sql string
 acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
 asccmd.ExecuteNonQuery() ' execute oledb commands non query only

i still get the same error message

Farhan_B 0 Junior Poster in Training · Answer 3 · 2012-02-29T06:01:51+00:00

Public acsda As New OleDbDataAdapter
 Public acsconn As New OleDbConnection
Public acsdr As OleDbDataReader
    Public strsql As String

    Sub connect()
        acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=..mdb
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
    End Sub
End Module




'sql statements
        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        cmd.ExecuteNonQuery() ' execute oledb commands non query only
        acscmd.Dispose()
        MsgBox("Saved")

this is my full code if it helps please can some help me i been at it for over 2 hours

kingsonprisonic 42 Posting Whiz in Training · Answer 4 · 2012-02-29T06:09:23+00:00

You do again wrong thing

Your code

Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
 acscmd.CommandText = strsql 'sets the sql string
 acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
' asccmd.ExecuteNonQuery() ' Here your problem Because acscmd is not asccmd

try

acscmd.ExecuteNonQuery()

Farhan_B 0 Junior Poster in Training · Answer 5 · 2012-02-29T06:16:52+00:00

i just did that and now i got a different error

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

i have had a look and the connection is open

kingsonprisonic 42 Posting Whiz in Training · Answer 6 · 2012-02-29T06:19:46+00:00

Try opening your connection before executing the query

Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand

acsconn.Open()'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

acsconn.Close()
        acscmd.Dispose()

Farhan_B 0 Junior Poster in Training · Answer 7 · 2012-02-29T06:27:47+00:00

now it looks like dis

The ConnectionString property has not been initialized.

my code is here

strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

        acsconn.Close()
        acscmd.Dispose()

Farhan_B 0 Junior Poster in Training · Answer 8 · 2012-02-29T07:06:13+00:00

Farhan_B 0 Junior Poster in Training

12 Years Ago

no errror happens but nothing happens now

kingsonprisonic 42 Posting Whiz in Training · Answer 9 · 2012-02-29T07:11:27+00:00

no errror happens but nothing happens now

What do you mean by that???

Farhan_B 0 Junior Poster in Training · Answer 10 · 2012-02-29T07:13:27+00:00

when i debug the system there no erro but when i click go button it does not do anything

kingsonprisonic 42 Posting Whiz in Training · Answer 11 · 2012-02-29T07:21:23+00:00

Ok now the time to post the full code of your current form... So please post it so we can track the error...

Farhan_B 0 Junior Poster in Training · Answer 12 · 2012-02-29T07:24:40+00:00

Imports System.Data.OleDb
Public Class Form2

    Dim cmd As OleDbCommand

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet.cg_security_user' table. You can move, or remove it, as needed.
        Me.Cg_security_userTableAdapter.Fill(Me.DataSet.cg_security_user)
        'TODO: This line of code loads data into the 'DataSet.cg_security_role' table. You can move, or remove it, as needed.
        Me.Cg_security_roleTableAdapter.Fill(Me.DataSet.cg_security_role)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    End Sub
    Sub connect()
        acsconn.ConnectionString = "[CODE][/CODE]..security.mdb"
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If



        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

        acsconn.Close()
        acscmd.Dispose()

    End Sub


End Class

kingsonprisonic 42 Posting Whiz in Training · Answer 13 · 2012-02-29T07:29:32+00:00

You do not call connect() Subroutine anywhere on your code so nothing happen..

try to call it on button click event.

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

Farhan_B 0 Junior Poster in Training · Answer 14 · 2012-02-29T07:46:10+00:00

now this error has returned
Not allowed to change the 'ConnectionString' property. The connection's current state is open.

kingsonprisonic 42 Posting Whiz in Training · Answer 15 · 2012-02-29T08:01:59+00:00

Ok OK OK.

Just replace your sub with this..

Sub connect()
        acsconn.ConnectionString = "..security.mdb"
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
 
 acsconn.Close()
 
        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only
 
        acsconn.Close()
        acscmd.Dispose()
 
    End Sub

Farhan_B 0 Junior Poster in Training · Answer 16 · 2012-02-29T08:10:31+00:00

im really sorry about this but again nothing seems to be happening

this is the code i have entered

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    End Sub
    Sub connect()
        acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\HP Sajad\Documents\Visual Studio 2010\Projects\login1\login1\security.mdb"
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
        acsconn.Close()

        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

        acsconn.Close()
        acscmd.Dispose()

    End Sub



End Class

kingsonprisonic 42 Posting Whiz in Training · Answer 17 · 2012-02-29T08:14:41+00:00

Oh my god. Please first read the post clearly

U again miss the call for subroutine..

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
connect()
    End Sub
    Sub connect()
        acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\HP Sajad\Documents\Visual Studio 2010\Projects\login1\login1\security.mdb"
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
        acsconn.Close()

        strsql = "insert into c_security_user (user_name, user_pwd,) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

        acsconn.Close()
        acscmd.Dispose()

    End Sub



End Class

try this code.. And notice here in between the Button click event i use connect()..
so connect() get called...

Farhan_B 0 Junior Poster in Training · Answer 18 · 2012-02-29T08:20:21+00:00

that has beeen resolved but now this error has come up

Syntax error in INSERT INTO statement.
at

acscmd.ExecuteNonQuery() ' execute oledb commands non query only

its either my bad luck or this code just dont like me

hericles 289 Master Poster Featured Poster · Answer 19 · 2012-02-29T09:01:41+00:00

Can you post up all of your code? Normally that error is accurate and so if it says the connection is closed it probably is.

Forget it... I didn't see there was this many pages. Ignore me:)

hericles 289 Master Poster Featured Poster · Answer 20 · 2012-02-29T09:05:45+00:00

But, to fix the problem, remove the comma after user_pwd

kingsonprisonic 42 Posting Whiz in Training · Answer 21 · 2012-02-29T10:30:59+00:00

Try this

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
connect()
    End Sub
    Sub connect()
        acsconn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\HP Sajad\Documents\Visual Studio 2010\Projects\login1\login1\security.mdb"
        acsconn.Open()
        If acsconn.State = ConnectionState.Open Then
            MsgBox("Connected")
        End If
        acsconn.Close()

        strsql = "insert into c_security_user (user_name, user_pwd) values ('" & txtuser.Text & "','" & txtpass.Text & "')"
        Dim acscmd As New OleDb.OleDbCommand ' the oledbcommand
        acscmd.CommandText = strsql 'sets the sql string
        acscmd.Connection = acsconn 'sets the connection to use the oledbcommand
        acsconn.Open() 'Open the connection first
        acscmd.ExecuteNonQuery() ' execute oledb commands non query only

        acsconn.Close()
        acscmd.Dispose()

    End Sub



End Class