Solved

Can memory variables in the vb program have the same name as field names in the tables?

Posted on 2004-09-03
8
271 Views
Last Modified: 2010-04-23
I have Dimed all my variable names with the same names as my field names.  Now, when I try to write the record is gives me this error message.

The name admDate is not permitted in this context.  Only contraints, variables, or expressions are allowed here.  Column names are not permitted.

????????


Here is the code
For m4 = 1 To 60
                    objCommand2.CommandText = "Insert Into itemAnalysisDetail " _
                                & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
                                "VALUES(admDate, studentId, teacherId, mathobj(m4), mathItemCorrectRespArray(m4), mathStudentRespArray(m4))"
                    objConnection2.Open()
                    objCommand2.ExecuteNonQuery()
                    objConnection2.Close()
                Next


Thanks
Kenny
0
Comment
Question by:kwh3856
  • 4
  • 4
8 Comments
 
LVL 7

Accepted Solution

by:
natloz earned 500 total points
ID: 11976997
I don't think you can do this actually...you need to split out your string...the values from your code cannot be in the "" of the main string...you have to concatenate the values together....note single quotes needed for strings...I am assuming your Arrays and IDs are numeric...if not you will have to add in the single quotes.

objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
"VALUES( '" & admDate & "'," & studentId & "," & teacherId "," &  readingElaobj(r4) & "," & readingElaItemCorrectRespArray(r4) & "," & readingElaStudentRespArray(r4) & ")"
0
 

Author Comment

by:kwh3856
ID: 11977030
Is there a better way to accomplish this task.  I just have a bunch of variables I want to save back down to the database.  Any other ideas?????

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 11977106
Thanks for the help.  If you know an easier way I sure would like to know.

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977115
This is pretty standard SQL string building in VB
If this is a SQL database...I find it much easier to create a stored procedure...then pass the values to the stored procedure, this way if  you have to make any changes to the the insert...you only need to change the stored procedure.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:kwh3856
ID: 11977142
Can you point me in a direction where I can learn about stored procedures????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977167
Here is a sample where a user logs into the system by passing values to a stored procedure in SQL...
The stored procedure is created using SQL Query Analyzer...

'************************************************
    'The user wants to attempt to login to the system
    '************************************************
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'Variables and objects
        Dim cDB As clsDBConn = New clsDBConn 'Connection Class
        Dim strConn As String = cDB.getStrConn 'Get connection string for database
        Dim oConn As New SqlConnection 'SQL connection object
        Dim intReturnID As Integer 'Return ID from Stored Procedures
        Dim oComm As SqlCommand 'SQL Command object
        Dim bFail As Boolean = False 'Test for failure

        'Validate form has data
        If Me.txtUsername.Text = "" Then
            MsgBox("Please enter a User Name.")
            Me.txtUsername.Focus()
            bFail = True
        Else
            If Me.txtPassword.Text = "" Then
                MsgBox("Please enter a Password.")
                Me.txtPassword.Focus()
                bFail = True
            End If
        End If

        If bFail = False Then 'All is well
            'Connect to SQL server and validate login
            Try
                'Pass connection string to SqlConnection
                oConn.ConnectionString = strConn

                'SQL Database statements
                oComm = New SqlCommand
                oComm.CommandType = CommandType.StoredProcedure
                oComm.CommandText = "spValidateLogin"
                oComm.Connection = oConn

                'Send in parameters
                oComm.Parameters.Add(New SqlParameter("@varUserName", SqlDbType.VarChar)).Value = Me.txtUsername.Text
                oComm.Parameters.Add(New SqlParameter("@varPassword", SqlDbType.VarChar)).Value = Me.txtPassword.Text

                'Return value from stored procedure
                Dim sParam As SqlParameter
                sParam = New SqlParameter
                sParam.ParameterName = "@ReturnID"
                sParam.SqlDbType = SqlDbType.Int
                sParam.Direction = ParameterDirection.Output
                oComm.Parameters.Add(sParam)

                'Open connection and execute oComm
                oConn.Open() 'Open connection
                oComm.ExecuteNonQuery() 'Execute stored procedure
                intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value
                oConn.Close() 'Close connection

                'Analyze Return value for errors
                If intReturnID < 0 Then 'All errors are less than 0
                    MsgBox("Invalid Login, please try again.")
                    Me.txtUsername.Focus()
                Else
                    Dim frmMain As frmMain = New frmMain
                    'Set the global variable for user logged in
                    _intUserID = intReturnID

                    'Open Main form and hide Login screen
                    frmMain.Show()
                    Me.Hide()
                End If

            Catch
                MsgBox("Error connecting to SQL Server")
            Finally
                oConn.Close() 'Close connection
            End Try
        End If
    End Sub


***************HERE IS THE STORED PROCEDURE*********
--Used to validate a user login...empty result is invalid
create proc spValidateLogin
     @varUsername varchar(25),
     @varPassword varchar(25),
     @ReturnID int output as
     
     SET NOCOUNT ON

     select * from tblUser where varUsername = @varUsername and varPassword = @varPassword

     if @@rowcount = 0 --if nothing came back from the query
          begin
               set @ReturnID = -3 --Invalid Login
          end
     else
          begin
               set @ReturnID = (select pkUserID from tblUser
                              where varUsername = @varUsername)
          end

     select @ReturnID
     return @ReturnID
GO
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977180
The Stored Procedure is not a VB.Net thing...it is a SQL Server thing...once you have created the Stored Procedure...you can call it from VB and Pass parameters to it.
0
 

Author Comment

by:kwh3856
ID: 11977190
Thanks...I think I understand...


Thanks
kenny
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
reports, calling from code. 3 30
Paging GridView 7 34
VB.Net - CSV to Oracle table 4 29
Saveas need to save a copy 16 11
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now