Solved

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

Posted on 2007-03-21
10
308 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 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

9 Experts available now in Live!

Get 1:1 Help Now