Solved

Add data in SQL using VB 2005 EE *** Urgent ***

Posted on 2007-03-21
10
314 Views
Last Modified: 2013-11-27
Experts,

I am trying to build a simple application in VB 2005 EE and SQL Server 2000. I am new to VB 2005 EE. However all I want is, a form with 2 labels, 2 text box and 2 picture boxes. Upon clicking the picture boxes, all data have to be stored in the database. However somehow I managed to add a new database source using the "Data" option. But I dont know how to add the data to sql. Kindly help me on the same.
0
Comment
Question by:Manju
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 6

Author Comment

by:Manju
ID: 18771353
Team,

Can anyone help me on this. I need to create a basic application.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18777592
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18777735
Dim cn As SqlConnection

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                      "Initial Catalog=Northwind;Trusted_Connection=Yes;"
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand()

cmd.CommandText = "Insert into YourTable  (YourField1, YourField2)  values('BLAH', 'Blue')"
cmd.ExecuteNonQuery()
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Author Comment

by:Manju
ID: 18781326
Hi,

I tried this code.

  Dim status

        status = "1"

        Dim cn As SqlConnection

        cn.ConnectionString = "Provider=SQLOLEDB;DataSource=BLRXTRACKDB; Catalog=iRESOLVE;Userid=ASGBot; Password=ASGPass;Trusted_Connection=Yes;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        cmd.CommandText = "Insert into Smiley  (Date, NTlogin, CaseNumber, CustomerNumber, Status)  values(label3.text, label6.text, textbox2.text, textbox1.text, status )"
        cmd.ExecuteNonQuery()

However I am getting an error -

Null Reference Exception was unhandled.
Object reference not set to an instance of an object in this line,

        cn.ConnectionString = "Provider=SQLOLEDB;DataSource=BLRXTRACKDB; Catalog=iRESOLVE;Userid=ASGBot; Password=ASGPass;Trusted_Connection=Yes;"
0
 
LVL 6

Author Comment

by:Manju
ID: 18783862
Joseph,

Thanks so much for your help.
I have made some changes on ur code and this one works perfectly fine.
However what I want is this. 2 text boxes will be filled by the user and 1 label is date and the other one is nt login. Kindly help me rectify this code.

Code:
 cn.ConnectionString = "Server=BLRXTRACKDB; Database=iresolve;User id= ASGBot; Password= ASGPass;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        Dim date1 As Date
        date1 = Label3.Text
        Dim login
        login = Label6.Text
        Dim casenumber
        casenumber = TextBox2.Text
        Dim customernumber
        customernumber = TextBox1.Text
        Dim status
        status = "1"

        cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"
        MsgBox("Updated")
        cmd.ExecuteNonQuery()

        cn.Close()

Using the above code, I am able to save data in the backend, however its saving as same. Its not getting the data what the user is entering. Need your help pls.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18783915
Your connection string is wrong:

Remove this "Provider=SQLOLEDB" in your connectionstring

Here is a useful link

http://www.connectionstrings.com/?carrier=sqlserver

SqlConnection (.NET)
 
Standard Security
 
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
 
  COPY TO CLIPBOARD
Standard Security alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.  
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
 
  COPY TO CLIPBOARD
Trusted Connection
 
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
 
  COPY TO CLIPBOARD
Trusted Connection alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.  
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
 
Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.  
 


0
 
LVL 6

Author Comment

by:Manju
ID: 18783917
Joseph,

Thanks so much for your help.
I have made some changes on ur code and this one works perfectly fine.
However what I want is this. 2 text boxes will be filled by the user and 1 label is date and the other one is nt login. Kindly help me rectify this code.

Code:
 cn.ConnectionString = "Server=BLRXTRACKDB; Database=iresolve;User id= ASGBot; Password= ASGPass;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        Dim date1 As Date
        date1 = Label3.Text
        Dim login
        login = Label6.Text
        Dim casenumber
        casenumber = TextBox2.Text
        Dim customernumber
        customernumber = TextBox1.Text
        Dim status
        status = "1"

        cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"
        MsgBox("Updated")
        cmd.ExecuteNonQuery()

        cn.Close()

Using the above code, I am able to save data in the backend, however its saving as same. Its not getting the data what the user is entering. Need your help pls.
0
 
LVL 15

Accepted Solution

by:
JackOfPH earned 500 total points
ID: 18784146
Replace this:
cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"

with this one:

cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('" & date1 & "','" & Ntlogin & "', '" & CustomerNumber & "', '" & CaseNumber & "', '" & Status & "')"
0
 
LVL 6

Author Comment

by:Manju
ID: 18785622
Bingo! Thanks Joe. I'll have more questions moving forward, and I want your expertise to help me learn.

Thanks.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18790049
I am glad I help.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 5 hours left to enroll

636 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