I have something to ask what will I do to this error, No value given for one or more required parameters

Question

0.00/5 (No votes)

See more:

VB

Imports System.Data.OleDb

Public Class Form1
    Dim cnn As New OleDb.OleDbConnection


    Private Sub RefreshData()
        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter(" SELECT login as [login], password as [password] FROM login ", cnn)

        Dim dt As New DataTable

        da.Fill(dt)

        dgvData.DataSource = dt
        cnn.Close()
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cmd As New OleDb.OleDbCommand

        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        cmd.Connection = cnn
        cmd.CommandText = " INSERT INTO login ([login], [password]) " & _
                        " VALUES(" & txtLog.Text & "," & txtPass.Text & ")"
        cmd.ExecuteNonQuery() ====> this is where the error occur

        RefreshData()

        cnn.Close()
   
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cnn = New OleDb.OleDbConnection
        Dim sql_query As String
        sql_query = "SELECT * FROM login"

        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source = " & Application.StartupPath & "\Church.mdb"
    End Sub

End Class

Posted 16-Oct-14 5:52am

Member 11158833

Updated 16-Oct-14 5:59am

Maciej Los

v2

Add a Solution

3 solutions

Solution 3

Hello, let this be a basic example for you, which you should follow suit. All though the solution you choose (Solution 1); worked. You are compromising your whole system by not using parameters. I stress that you change your solution from a security standpoint.

VB

'Please be sure to declare your cnn as your connection.         
            
                Query = "INSERT INTO login ([login], [password]) VALUES (@User, @Pass)"

'Do not concatenate queries. Use Parameters.

'Connect your query command to the connection.

                Dim QueryCom As New OleDb.OleDbCommand(Query, cnn)
                QueryCom.Parameters.AddWithValue("@username", txtLog.Text)

'@username and @guid is a placeholder for the declared declarations UserName and iUserGuid.
                QueryCom.Parameters.AddWithValue("@Pass", txtPass.Text)

'Check if the connection is open or not, if its closed, open it...
'Don't assume something, check it first.

                If cnn.State = ConnectionState.Closed Then
                    cnn.Open()
                End If
                QueryCom.ExecuteNonQuery()

                cnn.Close() 'Close the connection 
                cnn.Dispose() 'Dispose of it and its resources, don't just close it. 

'You should also wrap your communication statements in Try Catch Blocks to handle exceptions which may arise.

Posted 16-Oct-14 9:22am

Sheepings

Updated 16-Oct-14 10:17am

v3

Comments

Maciej Los 16-Oct-14 15:28pm

@CodingK, your answer is OK with one exception: there is no sql server database, it's ms access database.
Please, improve your answer to avoid down-voting.
Cheers,
Maciej

[no name] 16-Oct-14 15:36pm

Done, but I don't see how it makes a difference as the SqlConn was not declared in my code which I instructed to do in the example, and the OP could still have changed his cnn variable name to SqlConn. Rather than changing it several times in my example. ;)

I've renamed it to the OPs original variable name instead.

Maciej Los 16-Oct-14 15:46pm

(MySqlConn AND SqlConn) != OleDbConn
Different providers, true?

[no name] 16-Oct-14 16:03pm

Theoretically speaking, yes they are different providers, but it still makes no difference what variable name is declaring the connection type.

I could use these variable names for any of the following connections:
Dim SqlConn As MySqlConnection
Dim SqlConn As OleDbConnection
Dim SqlConn As SqlConnection
And regardless of the variable name, they will all work for the declared type. The variable name is not important, but the variable type represented by that variable is.

Where are you going with this?

Maciej Los 16-Oct-14 16:11pm

Have a look at this line: Dim QueryCom As New MySqlCommand(Query, cnn)
Do you know what i'm talking about?
And, of course, you're right. The name of variable is not important ;)

[no name] 16-Oct-14 16:20pm

Oh, One slipped by me :) thanks for spotting that, I would have missed it. lol I normally never work with access databases and alike so I don't know all of its functionality, except I know it uses different symbols and brackets in its queries as apposed to MySQL, and SQL. I am more of a SQL, MySQL guy, hopefully that edit will suffice.

Maciej Los 16-Oct-14 16:28pm

Great! My 5!

[no name] 16-Oct-14 16:33pm

Thank you for your vote of 5 Maciej.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-10-16T07:26:00

Your code is susceptible to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query instead:

VB

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Dim cmd As New OleDb.OleDbCommand

    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If

    cmd.Connection = cnn
    cmd.CommandText = "INSERT INTO login ([login], [password]) VALUES (?, ?)"
    cmd.Parameters.AddWithValue("p0", txtLog.Text)
    cmd.Parameters.AddWithValue("p1", txtPass.Text)

    cmd.ExecuteNonQuery()

    RefreshData()

    cnn.Close()
End Sub

Once you've fixed that gaping security vulnerability in your code, you need to look at your password storage. You're currently storing passwords in plain-text, which is a terrible idea. You should only ever store a salted hash of the password.

Salted Password Hashing - Doing it Right[^]

Maciej Los · Accepted Answer · 2014-10-16T06:04:00

Solution 1

On the first look... You have to add a ' character around the string values.

SQL

INSERT INTO login ([login], [password]) 
VALUES('stringValue','stringValue')

In case of numeric values, you have to use this:

SQL

INSERT INTO login ([NumericField1], [NumericField2]) 
VALUES(22,0)

In case of datetime values, you have to use this:

SQL

INSERT INTO login ([DateTimeField1], [DateTimeField2]) 
VALUES(#MM/dd/yyyy#,#MM/dd/yyyy#)

Finally, i suggest to use queries with named parameters[^]:

SQL

PARAMETERS [slogin] CHAR, [sPasswd] CHAR;
INSERT INTO [login] ([login],[password])
VALUES([slogin], sPassword)

Using parameters[^] is very good practice. You can add it via OleDbParameterCollection.AddWithValue Method[^]

Finally, i need to warn you: DO NOT use reserverd words[^]! It might be the reason of several troubles.

Cheers!

Posted 16-Oct-14 6:04am

Maciej Los

Updated 16-Oct-14 9:07am

v2

Comments

Member 11158833 16-Oct-14 12:24pm

thanks for the answer it helped ^^

Richard Deeming 16-Oct-14 13:22pm

Sorry, but you're encouraging the OP to continue using string concatenation to build SQL queries, so I've had to down-vote your answer.

Maciej Los 16-Oct-14 15:08pm

Is it better now?

Richard Deeming 16-Oct-14 15:10pm

Yes. I've updated my vote. :)

Maciej Los 16-Oct-14 15:11pm

Thank you, Richard ;)

[no name] 16-Oct-14 15:19pm

@Richard, Rated 5 as your response is correct.

This solution is also good and rated 5 stars. And it is imperative; you need to teach others that string concatenation is the wrong approach when using query string building, and you are leaving the OP open to Injection attacks while not using parametrized queries.

Maciej Los 16-Oct-14 15:21pm

Where do you see string concatenation now? What version of answer have you seen?

[no name] 16-Oct-14 15:26pm

Hi Maciej, good to see you followed mine and Richards advice, and updated your reply. I've also up-voted your reply. :)

Maciej Los 16-Oct-14 15:31pm

Thank you ;)
BTW: i followed Richard's advice and improved my answer before you posted your comment.

[no name] 16-Oct-14 15:39pm

Yes i see that, as my comment was posted after your edit. As a result, I've edited the original comment too. ;)

Maciej Los 16-Oct-14 15:42pm

;)