11 Replies - 503 Views - Last Post: 11 October 2011 - 11:06 AM Rate Topic: -----

#1 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

"Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:15 AM

I cant seem to debug this...
i double check the query and it is still throwing the error..

 sql = "INSERT INTO " & tbl & " (userName,userPassword,userAccessLevel, "
        sql = sql & "userDateCreated,userFirstName,userMiddleName,userLastName, "
        sql = sql & "lastUpdateDate,lastUpdateBy) "
        sql = sql & "VALUES (@u,@p,@lvl,@dateC,@Fname,@MName,@LName,@lud,@lub)"
        Dim p As SqlParameter() = New SqlParameter() _
        {MakeParam("@u", uName, SqlDbType.NVarChar), _
          MakeParam("@p", Convert.ToBase64String(bytHash), SqlDbType.NVarChar), _
           MakeParam("@lvl", lvl, SqlDbType.NVarChar), _
            MakeParam("@dateC", dateCreated, SqlDbType.NVarChar), _
             MakeParam("@Fname", Fname, SqlDbType.NVarChar), _
              MakeParam("@Mname", MName, SqlDbType.NVarChar), _
               MakeParam("@LName", LName, SqlDbType.NVarChar), _
                MakeParam("@lud", luDate, SqlDbType.NVarChar), _
                 MakeParam("@lub", UBy, SqlDbType.NVarChar)}

This post has been edited by polens: 11 October 2011 - 06:16 AM


Is This A Good Question/Topic? 0
  • +

Replies To: "Must Declare a Scalar Variable... " Error

#2 lordofduct  Icon User is offline

  • I'm a cheeseburger
  • member icon


Reputation: 2528
  • View blog
  • Posts: 4,630
  • Joined: 24-September 10

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:34 AM

where?

Is it in the SqlParameter bit? Have you tried reducing all the MakeParam calls and finding the one that causes this issue?

Don't just throw a bit of code at us and say, "solve this for me".
Was This Post Helpful? 0
  • +
  • -

#3 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:38 AM

oopss my bad..
wow your a bit harsh...
ok never mind i will solve it myself... thanks to you..
every parameter... i tried removing "@u" then the error goes to "@p" then so on...

PS:
i dont want to "Solve this for me" but i want you to point me to the right direction..
shhh

This post has been edited by polens: 11 October 2011 - 06:40 AM

Was This Post Helpful? -1
  • +
  • -

#4 lordofduct  Icon User is offline

  • I'm a cheeseburger
  • member icon


Reputation: 2528
  • View blog
  • Posts: 4,630
  • Joined: 24-September 10

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:43 AM

I did, I pointed you in the direction of how to solve a problem yourself. That way you don't need to come running to everyone else for help.

Once you've shown effort on your own part, then we are happy and joyous to point you in a direction. But this here... there is no direction other than telling you exactly what the answer is by doing all the work for you, or telling you how to debug it yourself.
Was This Post Helpful? -1
  • +
  • -

#5 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:50 AM

Then why did they created this forums for?
Was This Post Helpful? 0
  • +
  • -

#6 lordofduct  Icon User is offline

  • I'm a cheeseburger
  • member icon


Reputation: 2528
  • View blog
  • Posts: 4,630
  • Joined: 24-September 10

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 06:57 AM

to help, assistance.

We don't do the work for you. We assist you in doing the work for yourself. Like a teacher, tutor, or study mate. Your teachers, tutors, and study mates don't 'do' your work for you do they?

If you could bring me some specifics about your problem, what it is you're trying to do, and maybe some information about how you've tried to remedy it yourself. Maybe even go through the params yourself and attempt to find the trouble area. Then I'd LOVE to give some assistance.

This post has been edited by lordofduct: 11 October 2011 - 06:58 AM

Was This Post Helpful? -1
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8897
  • View blog
  • Posts: 33,369
  • Joined: 12-June 08

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 07:05 AM

A few things.. What's the full error message? It'll tell you the variable that is not declared and you can work back from there.

What's your string "SQL" look like after it's done being appended? It's possible a table name isn't working out right, or a missing character.

Third, are you certain your column names are correctly typed out for that table?

That should do for the moment.
Was This Post Helpful? 0
  • +
  • -

#8 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 07:09 AM

solve it already.... thanks to your "Harsh Words"
i change my function method into a boolean and my runNonQuery function to Boolean that solved the problem.....it was datatable at first because in the presentation layer i have declare a datatable for me to check if it inserted the data (dt.rows.count > 0)...

so now professor how can i know if "it succesfully" inserted the data?
i am using 2tier layering(Data Access Layer and Presentation Layer)

PS:
Yes i am a student but this project is for a company the will pay us..
not thesis but a job i guess.
Was This Post Helpful? 0
  • +
  • -

#9 lordofduct  Icon User is offline

  • I'm a cheeseburger
  • member icon


Reputation: 2528
  • View blog
  • Posts: 4,630
  • Joined: 24-September 10

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 07:38 AM

You're welcome for the "harsh words", otherwise maybe someone else would have done it for you and you wouldn't have learned anything today.

Guess what, now you know a little bit more about debugging. And you're a more valuable individual in the realm of software development. You have a million more little lessons like this and one day you might be able to call yourself a programmer.

If you take what I'm saying now as "harsh words", I'd like to know in what world you live where everyone speaks in platitudes and puppy dog tails. Sorry son, this is the real world, you gotta put some damn effort in!
Was This Post Helpful? 0
  • +
  • -

#10 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 09:17 AM

haha as if you really help at all.... haha thanks anyway for your "Very Helpful" response. . .
Was This Post Helpful? 0
  • +
  • -

#11 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1817
  • View blog
  • Posts: 4,625
  • Joined: 14-March 10

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 09:18 AM

View Postpolens, on 11 October 2011 - 07:54 PM, said:

so now how can i know if "it succesfully" inserted the data?
i am using 2tier layering(Data Access Layer and Presentation Layer)

You need a message if the work is completed. Just at the end of the working code put a message to show if the work is done. And in the catch part, where the execution happens if error occurs, give a message to show a failure.
Was This Post Helpful? 0
  • +
  • -

#12 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: "Must Declare a Scalar Variable... " Error

Posted 11 October 2011 - 11:06 AM

i cant really do that because i only have 1 method for inserting new records which is the "runNonQuery" method. .
for example:
This is the runNonQuery Function
Private Function runNonQuery(ByVal name As String, ByVal param As SqlParameter()) As Boolean

        Try
            With com
                .com.Connection = con.SqlConnection
                .com.CommandType = CommandType.Text
                .com.CommandText = name
                Dim da As SqlDataAdapter = New SqlDataAdapter
                da.SelectCommand = .com

                If param Is Nothing Then
                    GoTo execute
                Else
                    For Each p As SqlParameter In param
                        da.SelectCommand.Parameters.Add(p)
                    Next
                End If
execute:       
                .com.ExecuteNonQuery()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.IsDbOpen()
        End Try
        Return True
    End Function

and this is where the sql function
Public Function AddNewUser(ByVal tbl As String, _
                               ByVal uName As String, ByVal UPass As String, _
                               ByVal lvl As String, ByVal dateCreated As Date, _
                               ByVal Fname As String, ByVal MName As String, _
                               ByVal LName As String, ByVal luDate As Date, _
                               ByVal UBy As String) As Boolean

        'Encrypt Password
        Dim mhash As HashAlgorithm = New SHA1CryptoServiceProvider
        Dim bytValue() As Byte = System.Text.Encoding.UTF8.GetBytes(UPass)
        Dim bytHash() As Byte = mhash.ComputeHash(bytValue)
        mhash.Clear()

        sql = "INSERT INTO " & tbl & " (userName,userPassword,userAccessLevel, "
        sql = sql & "userDateCreated,userFirstName,userMiddleName,userLastName, "
        sql = sql & "lastUpdateDate,lastUpdateBy) "
        sql = sql & "VALUES (@u,@p,@lvl,@dateC,@Fname,@MName,@LName,@lud,@lub)"
        Dim p As SqlParameter() = New SqlParameter() _
        {MakeParam("@u", uName, SqlDbType.NVarChar), _
          MakeParam("@p", Convert.ToBase64String(bytHash), SqlDbType.NVarChar), _
           MakeParam("@lvl", lvl, SqlDbType.NVarChar), _
            MakeParam("@dateC", dateCreated, SqlDbType.NVarChar), _
             MakeParam("@Fname", Fname, SqlDbType.NVarChar), _
              MakeParam("@Mname", MName, SqlDbType.NVarChar), _
               MakeParam("@LName", LName, SqlDbType.NVarChar), _
                MakeParam("@lud", luDate, SqlDbType.NVarChar), _
                 MakeParam("@lub", UBy, SqlDbType.NVarChar)}
        Return runNonQuery(sql, p)
    End Function

the runNonQuery Function can be used by another query(delete/update)

This post has been edited by polens: 11 October 2011 - 11:07 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1